now i want to calculate teacher attendance between 2 dates each teacher is assigned to one class when teacher takes his class then his teacherid is get inserted to teacherid col(with replacement=0) and when other techer takes the class then his teacherid get inserted in replacementteacher col(with teacherid=0)
SELECT classid, SUM(Attenid) TotalLectures, SUM(CASE teacherid WHEN 1 THEN 1 ELSE 0 END) AS PresentLectures, SUM(CASE WHEN teacherid = 0 AND repleceteacherid > 0 THEN 1 ELSE 0 END) AS AbsentLectures FROM ATTENDANCE WHERE currentdat >= '2012-06-07 00:00:00.000' AND currentdat <= '2012-08-07 00:00:00.000' GROUP BY classid
sorry but how do you determine a teachers absent lectures from current table design? As i understand it will just have replacementteacherid as teacher who replaced but it wont have any details of original lecture who was supposed to take class as its having 0 value
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
query help
Posted - 07/11/2012 : 03:58:08
