查詢篇-學員出勤課時消耗金額高精度分析

當一名學員交了學費，不能算是學校實際收入，必需將學員的課程授完，才算結束。那么在學員出勤的過程中，有時候我們需要動態的計算學員某段時間內消耗了多少課時，對應的學費應當是多少？看似簡單的問題，實際操作如果要做到高精準度并不容易。因為優惠的客觀存在，還有某段時間內學員存在多次購買課時，并且單價不同都會影響結果的精準度。麥田軟件一直在研究這個課題，并且已經將這個模塊成熟化，性能也做了大幅度優化，目前連普通的ACCESS版也可以使用此功能，處于行業領先地位。下面就展示給大家如何使用此功能，并且如何進行分析統計？

第一步、系統-基礎參數-學員/員工考勤設置：開啟高精度出勤課時價值計算

第二步、重計歷史數據：

查詢設計：

查詢老師所帶學員課時金額總消耗


Select
Users.TrueName as 老師,
sum(Attend.Lessons) as 總課時,
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:開始日期}
and LessonDegree.EndDate <= {@EndDate:結束日期}
and Class.SchoolID in ( {@SchoolID:校區} )
group by LessonDegree.TeacherID,Users.TrueName



查詢課程實時消費（子查詢學員消費）

主查詢：

Select
Class.CourseName as 課程,
sum(Attend.Lessons) as 總課時,
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:開始日期}
and LessonDegree.EndDate <= {@EndDate:結束日期}
and Class.SchoolID in ( {@SchoolID:校區} )
group by Class.CourseID,Class.CourseName

子查詢：

Select
StuClass.StudentName as 學員,
sum(Attend.Lessons) as 總課時,
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