Below I will put the structure of some tables to illustrate what I am trying to do.
Considering that the time of each student's class is 30 minutes, I would like to select the student's schedule, but I need to group the records in which the class schedules are sequential (30 minutes each, without interval) and the teacher and subject are the same
I'm using SQL Server 2017.
student
id name
1 Oliver
2 Jack
3 Harry
teacher
id name
1 Amelia
2 Olivia
subject
id subject
1 Mathematics
2 Science
schedule
id startdatetime idstudent idteacher idsubject
1 2019-05-30 08:00 1 1 2
2 2019-05-30 08:40 1 1 2
3 2019-05-30 09:10 1 1 2
3 2019-05-30 09:40 1 2 2
4 2019-05-30 10:10 1 2 1
When selecting by idstudent, I would like to display the result in a grouped as follows:
Qty startdatetime teacher subject
1 2019-05-30 08:00 Amelia Science
2 2019-05-30 08:40 Amelia Science grouped in a single row(qty 2) because the class time has 30 minutes without interval, teacher and subject are the same.
1 2019-05-30 09:40 Olivia Science
1 2019-05-30 10:10 Olivia Mathematics
Thanks in advance!
This is a type of group-and-islands problem. I am focusing only on the schedule table. You can join in other tables as you see fit.
To determine where a group begins, use lag(). Then a cumulative sum defines the group and aggregation gets what you want:
select count(*) as qty,
idstudent, idteacher, idsubject
from (select s.*,
sum(case when prev_sdt > dateadd(minute, -30, startdatetime)
then 1 else 0
end) over (partition by s.idstudent, s.idteacher, s.idsubject order by s.startdatetime) as grp
from (select s.*,
lag(s.startdatetime) over (partition by s.idstudent, s.idteacher, s.idsubject order by s.startdatetime) as prev_sdt
from schedule s
) s
) s
group by by idstudent, idteacher, idsubject, grp
order by min(startdatetime);