查询篇-学员出勤课时消耗金额高精度分析

当一名学员交了学费，不能算是学校实际收入，必需将学员的课程授完，才算结束。那么在学员出勤的过程中，有时候我们需要动态的计算学员某段时间内消耗了多少课时，对应的学费应当是多少？看似简单的问题，实际操作如果要做到高精准度并不容易。因为优惠的客观存在，还有某段时间内学员存在多次购买课时，并且单价不同都会影响结果的精准度。麦田软件一直在研究这个课题，并且已经将这个模块成熟化，性能也做了大幅度优化，目前连普通的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