View Historical Attendance data for a day every 10 min
up vote
0
down vote
favorite
1
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
sql tsql sql-server-2016
edited Nov 5 at 2:46
Gordon Linoff
740k32285389
asked Nov 5 at 2:44
Senthil_Arun
36139
|
show 1 more comment
up vote
0
down vote
favorite
1
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
sql tsql sql-server-2016
edited Nov 5 at 2:46
Gordon Linoff
740k32285389
asked Nov 5 at 2:44
Senthil_Arun
36139
1
Please explain the logic for the "OutPut".
– Gordon Linoff
Nov 5 at 2:46
I need the count of students for every 10 minute for a given day, groupby Class, Starttime, Endtime
– Senthil_Arun
Nov 5 at 2:48
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
Nov 5 at 2:51
@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?
– Avi
Nov 5 at 2:53
The "Output" is the expected result, I dont know how to bring that.I can create a time dimension if needed
– Senthil_Arun
Nov 5 at 2:55
|
show 1 more comment
up vote
0
down vote
favorite
1
up vote
0
down vote
favorite
1
1
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
sql tsql sql-server-2016
edited Nov 5 at 2:46
Gordon Linoff
740k32285389
asked Nov 5 at 2:44
Senthil_Arun
36139
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
sql tsql sql-server-2016
sql tsql sql-server-2016
edited Nov 5 at 2:46
Gordon Linoff
740k32285389
asked Nov 5 at 2:44
Senthil_Arun
36139
edited Nov 5 at 2:46
Gordon Linoff
740k32285389
asked Nov 5 at 2:44
Senthil_Arun
36139
edited Nov 5 at 2:46
Gordon Linoff
740k32285389
edited Nov 5 at 2:46
Gordon Linoff
740k32285389
edited Nov 5 at 2:46
Gordon Linoff
740k32285389
740k32285389
asked Nov 5 at 2:44
Senthil_Arun
36139
asked Nov 5 at 2:44
Senthil_Arun
36139
asked Nov 5 at 2:44
Senthil_Arun
36139
36139
1
Please explain the logic for the "OutPut".
– Gordon Linoff
Nov 5 at 2:46
I need the count of students for every 10 minute for a given day, groupby Class, Starttime, Endtime
– Senthil_Arun
Nov 5 at 2:48
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
Nov 5 at 2:51
@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?
– Avi
Nov 5 at 2:53
The "Output" is the expected result, I dont know how to bring that.I can create a time dimension if needed
– Senthil_Arun
Nov 5 at 2:55
|
show 1 more comment
1
Please explain the logic for the "OutPut".
– Gordon Linoff
Nov 5 at 2:46
I need the count of students for every 10 minute for a given day, groupby Class, Starttime, Endtime
– Senthil_Arun
Nov 5 at 2:48
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
Nov 5 at 2:51
@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?
– Avi
Nov 5 at 2:53
The "Output" is the expected result, I dont know how to bring that.I can create a time dimension if needed
– Senthil_Arun
Nov 5 at 2:55
1
1
Please explain the logic for the "OutPut".
– Gordon Linoff
Nov 5 at 2:46
Please explain the logic for the "OutPut".
– Gordon Linoff
Nov 5 at 2:46
I need the count of students for every 10 minute for a given day, groupby Class, Starttime, Endtime
– Senthil_Arun
Nov 5 at 2:48
I need the count of students for every 10 minute for a given day, groupby Class, Starttime, Endtime
– Senthil_Arun
Nov 5 at 2:48
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
Nov 5 at 2:51
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
Nov 5 at 2:51
@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?
– Avi
Nov 5 at 2:53
@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?
– Avi
Nov 5 at 2:53
The "Output" is the expected result, I dont know how to bring that.I can create a time dimension if needed
– Senthil_Arun
Nov 5 at 2:55
The "Output" is the expected result, I dont know how to bring that.I can create a time dimension if needed
– Senthil_Arun
Nov 5 at 2:55
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
0
down vote
You need to generate the times. One way uses a recursive CTE. Then there are various ways to get the count.
with times as (
select cast('2018-10-31 07:00:00' as datetime) dt
union all
select dateadd(minute, 10, dt)
from times
where dateadd(minute, 10, dt) < '2018-10-31 08:00:00'
)
select t.dt,
(select sum(case when eventtype = 'I' then 1 else -1 end)
from attendance a
where a.EventTime <= t.dt
) as attendance
from times;
answered Nov 5 at 3:07
Gordon Linoff
740k32285389
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
You need to generate the times. One way uses a recursive CTE. Then there are various ways to get the count.
with times as (
select cast('2018-10-31 07:00:00' as datetime) dt
union all
select dateadd(minute, 10, dt)
from times
where dateadd(minute, 10, dt) < '2018-10-31 08:00:00'
)
select t.dt,
(select sum(case when eventtype = 'I' then 1 else -1 end)
from attendance a
where a.EventTime <= t.dt
) as attendance
from times;
answered Nov 5 at 3:07
Gordon Linoff
740k32285389
add a comment |
up vote
0
down vote
You need to generate the times. One way uses a recursive CTE. Then there are various ways to get the count.
with times as (
select cast('2018-10-31 07:00:00' as datetime) dt
union all
select dateadd(minute, 10, dt)
from times
where dateadd(minute, 10, dt) < '2018-10-31 08:00:00'
)
select t.dt,
(select sum(case when eventtype = 'I' then 1 else -1 end)
from attendance a
where a.EventTime <= t.dt
) as attendance
from times;
answered Nov 5 at 3:07
Gordon Linoff
740k32285389
add a comment |
up vote
0
down vote
up vote
0
down vote
You need to generate the times. One way uses a recursive CTE. Then there are various ways to get the count.
with times as (
select cast('2018-10-31 07:00:00' as datetime) dt
union all
select dateadd(minute, 10, dt)
from times
where dateadd(minute, 10, dt) < '2018-10-31 08:00:00'
)
select t.dt,
(select sum(case when eventtype = 'I' then 1 else -1 end)
from attendance a
where a.EventTime <= t.dt
) as attendance
from times;
answered Nov 5 at 3:07
Gordon Linoff
740k32285389
You need to generate the times. One way uses a recursive CTE. Then there are various ways to get the count.
with times as (
select cast('2018-10-31 07:00:00' as datetime) dt
union all
select dateadd(minute, 10, dt)
from times
where dateadd(minute, 10, dt) < '2018-10-31 08:00:00'
)
select t.dt,
(select sum(case when eventtype = 'I' then 1 else -1 end)
from attendance a
where a.EventTime <= t.dt
) as attendance
from times;
answered Nov 5 at 3:07
Gordon Linoff
740k32285389
answered Nov 5 at 3:07
Gordon Linoff
740k32285389
answered Nov 5 at 3:07
Gordon Linoff
740k32285389
answered Nov 5 at 3:07
Gordon Linoff
740k32285389
740k32285389
add a comment |
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147663%2fview-historical-attendance-data-for-a-day-every-10-min%23new-answer', 'question_page');
}
);
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
1
Please explain the logic for the "OutPut".
– Gordon Linoff
Nov 5 at 2:46
I need the count of students for every 10 minute for a given day, groupby Class, Starttime, Endtime
– Senthil_Arun
Nov 5 at 2:48
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
Nov 5 at 2:51
@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?
– Avi
Nov 5 at 2:53
The "Output" is the expected result, I dont know how to bring that.I can create a time dimension if needed
– Senthil_Arun
Nov 5 at 2:55