String lsSql = "SELECT cs.ClassScheduleId, c.EDCId, c.ClassName, c.ClassId, c.TutorId, c.StartDate, c.EndDate, "
+ "(SELECT COUNT(*) FROM tblClassSchedules WHERE EDCId = c.EDCId AND ClassId = c.ClassId) As Sessions, "
+ "dbo.EDCFnClassDay(cs.EDCId, cs.ClassId) as Day, "
+ "cs.Date, cs.StartTime, c.Duration, cs.ClassroomId, "
+ "(SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) As Atd, " + "CAST( "
+ "(SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) "
+ "* "
+ "CAST(100 as decimal(18,5)) "
+ "/ "
+ "(SELECT COUNT(*) FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ "AS decimal(18,5)) As Atd_pct, " + "CASE "
+ "WHEN DATEDIFF(d, cs.Date, GETDATE()) >=0 THEN "
+ " (SELECT COUNT(*) FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ " - "
+ " (SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) "
+ "ELSE "
+ " 0 "
+ "END As Abs, " + "CASE "
+ "WHEN DATEDIFF(d, cs.Date, GETDATE()) >=0 THEN "
+ " ( "
+ " (SELECT COUNT(*) FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ " - "
+ " (SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) "
+ " ) "
+ " * "
+ " CAST(100 as decimal(18,5)) "
+ " / "
+ " (SELECT COUNT(*) FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ "ELSE "
+ " 0.00 "
+ "END As Abs_pct, " + "dbo.EDCFnClassAbsStudent(cs.ClassScheduleId, GETDATE()) As Abs_Stu_Id " + "FROM tblClassSchedules cs "
+ "INNER JOIN tblClasses c ON cs.EDCId = c.EDCId AND cs.ClassId = c.ClassId
出现错误为"Divide by zero error encountered."
也就是除数为 0 了
请问这么去修改???
+ "(SELECT COUNT(*) FROM tblClassSchedules WHERE EDCId = c.EDCId AND ClassId = c.ClassId) As Sessions, "
+ "dbo.EDCFnClassDay(cs.EDCId, cs.ClassId) as Day, "
+ "cs.Date, cs.StartTime, c.Duration, cs.ClassroomId, "
+ "(SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) As Atd, " + "CAST( "
+ "(SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) "
+ "* "
+ "CAST(100 as decimal(18,5)) "
+ "/ "
+ "(SELECT COUNT(*) FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ "AS decimal(18,5)) As Atd_pct, " + "CASE "
+ "WHEN DATEDIFF(d, cs.Date, GETDATE()) >=0 THEN "
+ " (SELECT COUNT(*) FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ " - "
+ " (SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) "
+ "ELSE "
+ " 0 "
+ "END As Abs, " + "CASE "
+ "WHEN DATEDIFF(d, cs.Date, GETDATE()) >=0 THEN "
+ " ( "
+ " (SELECT COUNT(*) FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ " - "
+ " (SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) "
+ " ) "
+ " * "
+ " CAST(100 as decimal(18,5)) "
+ " / "
+ " (SELECT COUNT(*) FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ "ELSE "
+ " 0.00 "
+ "END As Abs_pct, " + "dbo.EDCFnClassAbsStudent(cs.ClassScheduleId, GETDATE()) As Abs_Stu_Id " + "FROM tblClassSchedules cs "
+ "INNER JOIN tblClasses c ON cs.EDCId = c.EDCId AND cs.ClassId = c.ClassId
出现错误为"Divide by zero error encountered."
也就是除数为 0 了
请问这么去修改???
+ "(SELECT COUNT(*) FROM tblClassSchedules WHERE EDCId = c.EDCId AND ClassId = c.ClassId) As Sessions, "
+ "dbo.EDCFnClassDay(cs.EDCId, cs.ClassId) as Day, "
+ "cs.Date, cs.StartTime, c.Duration, cs.ClassroomId, "
+ "(SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) As Atd, " + "CAST( "
+ "(SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) "
+ "* "
+ "CAST(100 as decimal(18,5)) "
+ "/ "
+ "(SELECT CASE WHEN COUNT(*)=0 THEN 1 ELSE COUNT(*) END FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ "AS decimal(18,5)) As Atd_pct, " + "CASE "
+ "WHEN DATEDIFF(d, cs.Date, GETDATE()) >=0 THEN "
+ " (SELECT COUNT(*) FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ " - "
+ " (SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) "
+ "ELSE "
+ " 0 "
+ "END As Abs, " + "CASE "
+ "WHEN DATEDIFF(d, cs.Date, GETDATE()) >=0 THEN "
+ " ( "
+ " (SELECT COUNT(*) FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ " - "
+ " (SELECT COUNT(*) FROM tblAttendances WHERE ClassScheduleId = cs.ClassScheduleId) "
+ " ) "
+ " * "
+ " CAST(100 as decimal(18,5)) "
+ " / "
+ " (SELECT CASE WHEN COUNT(*)=0 THEN 1 ELSE COUNT(*) END FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ "ELSE "
+ " 0.00 "
+ "END As Abs_pct, " + "dbo.EDCFnClassAbsStudent(cs.ClassScheduleId, GETDATE()) As Abs_Stu_Id " + "FROM tblClassSchedules cs "
+ "INNER JOIN tblClasses c ON cs.EDCId = c.EDCId AND cs.ClassId = c.ClassId
+ "AS decimal(18,5)) As Atd_pct, "
这样些好像还是不行哦??
+ "cast((SELECT CASE WHEN COUNT(*)=0 THEN 1 ELSE COUNT(*) END FROM tblEnrolls WHERE EDCId = cs.EDCId AND ClassId = cs.ClassId) "
+ "AS decimal(18,5))) As Atd_pct, "