查詢篇-學(xué)員出勤課時(shí)消耗金額高精度分析

當(dāng)一名學(xué)員交了學(xué)費(fèi)，不能算是學(xué)校實(shí)際收入，必需將學(xué)員的課程授完，才算結(jié)束。那么在學(xué)員出勤的過(guò)程中，有時(shí)候我們需要?jiǎng)討B(tài)的計(jì)算學(xué)員某段時(shí)間內(nèi)消耗了多少課時(shí)，對(duì)應(yīng)的學(xué)費(fèi)應(yīng)當(dāng)是多少？看似簡(jiǎn)單的問(wèn)題，實(shí)際操作如果要做到高精準(zhǔn)度并不容易。因?yàn)閮?yōu)惠的客觀存在，還有某段時(shí)間內(nèi)學(xué)員存在多次購(gòu)買課時(shí)，并且單價(jià)不同都會(huì)影響結(jié)果的精準(zhǔn)度。麥田軟件一直在研究這個(gè)課題，并且已經(jīng)將這個(gè)模塊成熟化，性能也做了大幅度優(yōu)化，目前連普通的ACCESS版也可以使用此功能，處于行業(yè)領(lǐng)先地位。下面就展示給大家如何使用此功能，并且如何進(jìn)行分析統(tǒng)計(jì)？

第一步、系統(tǒng)-基礎(chǔ)參數(shù)-學(xué)員/員工考勤設(shè)置：開(kāi)啟高精度出勤課時(shí)價(jià)值計(jì)算

第二步、重計(jì)歷史數(shù)據(jù)：

查詢?cè)O(shè)計(jì)：

查詢老師所帶學(xué)員課時(shí)金額總消耗


Select
Users.TrueName as 老師,
sum(Attend.Lessons) as 總課時(shí),
sum(Attend.Price) as 總金額
 
from Attend,LessonDegree,Class,Users
where LessonDegree.LessonDegreeID = Attend.LessonDegreeID
and LessonDegree.TeacherID = Users.UserID
and LessonDegree.ClassID = Class.ClassID
and LessonDegree.StartDate >= {@StartDate:開(kāi)始日期}
and LessonDegree.EndDate <= {@EndDate:結(jié)束日期}
and Class.SchoolID in ( {@SchoolID:校區(qū)} )
group by LessonDegree.TeacherID,Users.TrueName



查詢課程實(shí)時(shí)消費(fèi)（子查詢學(xué)員消費(fèi)）

主查詢：

Select
Class.CourseName as 課程,
sum(Attend.Lessons) as 總課時(shí),
sum(Attend.Price) as 總金額,
Class.CourseID as ShowKey
from Attend,LessonDegree,Class,Users
where LessonDegree.LessonDegreeID = Attend.LessonDegreeID
and LessonDegree.TeacherID = Users.UserID
and LessonDegree.ClassID = Class.ClassID
and LessonDegree.StartDate >= {@StartDate:開(kāi)始日期}
and LessonDegree.EndDate <= {@EndDate:結(jié)束日期}
and Class.SchoolID in ( {@SchoolID:校區(qū)} )
group by Class.CourseID,Class.CourseName

子查詢：

Select
StuClass.StudentName as 學(xué)員,
sum(Attend.Lessons) as 總課時(shí),
sum(Attend.Price) as 總金額
from Attend,LessonDegree,Class,StuClass
where LessonDegree.LessonDegreeID = Attend.LessonDegreeID
and Attend.StuClassID = StuClass.StuClassID
and LessonDegree.ClassID = Class.ClassID
and LessonDegree.StartDate >= {@StartDate}
and LessonDegree.EndDate <= {@EndDate}
and Class.SchoolID in ( {@SchoolID} )
and Class.CourseID = {@ShowKey}
group by StuClass.StudentID,StuClass.StudentName