View Historical Attendance data for a day every 10 min

1487 views sql
2

I have a table which contains Students Attendance, the schema is

StudentId   ClassId EventType   EventTime   
1           1       I           2018-10-31 07:00:00 AM
2           1       I           2018-10-31 07:02:00 AM
1           1       O           2018-10-31 07:31:00 AM
3           1       I           2018-10-31 07:45:00 AM

OutPut

ClassId     StudentCount    StartTime               EndTime
1           2               2018-10-31 07:00:00 AM  2018-10-31 07:10:00 AM
1           2               2018-10-31 07:10:01 AM  2018-10-31 07:20:00 AM
1           2               2018-10-31 07:20:01 AM  2018-10-31 07:30:00 AM
1           1               2018-10-31 07:30:01 AM  2018-10-31 07:40:00 AM
1           2               2018-10-31 07:40:01 AM  2018-10-31 07:50:00 AM

answered question

Please explain the logic for the "OutPut".

I need the count of students for every 10 minute for a given day, groupby Class, Starttime, Endtime

I found a way to group by data GROUP BY Class_ID_Location, (DATEDIFF(MINUTE, '1990-01-01T00:00:00', Event_Time) / 10), but this results start and end time only when there is any entry in the attendance table for the given period, i want for every 10 min irrespective whether an entry exists in Attendance table, pleaselte me know if you still need more info

@Senthil_Arun, In your output I see count of 2 students from 7:10 to 7:20 when there is no event time for any students in your sample data, how did you generate the sample output for row 2?

The "Output" is the expected result, I dont know how to bring that.I can create a time dimension if needed

1 Answer

12

Hope this helps, this code is not tested there might be syntax error,

  Select A.classid,O.StartTime,O.EndTime, count(A.StudentId) as Count
  From StudentsAttendance A
  inner join OutPut O on A.ClassId = O.ClassId
  and A.EventTime between O.StartTime and O.EndTime
  Group by A.classid,O.StartTime,O.EndTime 

posted this

Have an answer?

JD

Please login first before posting an answer.