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

2014年08月18日 12:38
点击率:8702

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