question

diruser avatar image
diruser asked

Deduction time from multiple records but exclude overlapping.

I have a database with PrimaryKey, Type, StudentID, Date, StartTime, EndTime. If a records Type is A then i need to deduct all times that overlap with Time A if the other records is not A, and the StudentID, Dates are the same, A 15 25 2/9/2014 3:00 4:00 B 15 25 2/9/2014 3:30 4:30 C 15 25 2-9-2014 3:15 3:45 I have it working where all time from B & C are deducted form A, it is deduction 60 but that leaves me at 0, since B & C overlap, it should only deduct 45 min. I do this by joining the table to its self and matching dates and StudentID and make sure the PrimaryKey is not the same. This works great unless the non A records have overlapping time. I was thinking of loading into an array but not not sure how. Any help is appropriated.
sql-server-2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
iainrobertson avatar image
iainrobertson answered
There might be a more elegant way to do it, but here's one method using a tally table. The problem as I see it is that you can't have any certainty over where the time slots to be allocated will fall, or how many there are. This ruled out any solutions using windowed functions that I could think of. So you're left with brute force as the best option :) Note that I've forced calculation at the minute level here. It looks like your data is in 15 minute slots, so you might want to change the solution to use a tally table that increments in 15 minute steps, as opposed to the 1 minute steps I've used . This would definitely improve performance as it significantly reduces the amount of data compared. -- create test data create table #student_time (PrimaryKey int identity, RecordType char(1), StudentID int, ActivityDate date, StartTime time, EndTime time) go insert #student_time (RecordType, StudentID, ActivityDate, StartTime, EndTime) select 'A', '15', '2 Sep 2014', '3:00', '4:00' union all select 'B', '15', '2 Sep 2014', '3:30', '4:30' union all select 'C', '15', '2 Sep 2014', '3:15', '3:45' go -- create a time table ; with e1(n) as ( select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 ) , e2(n) as (select 1 from e1 a, e1 b) , e4(n) as (select 1 from e2 a, e2 b) , tally as (select top 10000 n = row_number() over (order by (select null)) from e4) select n_time = isnull(cast(dateadd(minute,n-1,'00:00:00') as time),'00:00:00') into #tally_time from tally where n 'A' and tt.n_time between st.StartTime and cast(dateadd(minute,-1,EndTime) as time) -- grouping up gives distinct allocatable time select StudentID, ActivityDate, n_time from #student_time st cross join #tally_time tt where RecordType 'A' and tt.n_time between st.StartTime and cast(dateadd(minute,-1,EndTime) as time) group by StudentID, ActivityDate, n_time -- combine the two to compare the times ; with cte as ( select * from #student_time st cross join #tally_time tt where RecordType = 'A' and tt.n_time between st.StartTime and cast(dateadd(minute,-1,EndTime) as time) ) select * from cte join ( select StudentID, ActivityDate, n_time from #student_time st cross join #tally_time tt where RecordType 'A' and tt.n_time between st.StartTime and cast(dateadd(minute,-1,EndTime) as time) group by StudentID, ActivityDate, n_time ) allocatable on cte.StudentID = allocatable.StudentID and cte.ActivityDate = allocatable.ActivityDate and cte.n_time = allocatable.n_time -- group and count to return the result ; with cte as ( select * from #student_time st cross join #tally_time tt where RecordType = 'A' and tt.n_time between st.StartTime and cast(dateadd(minute,-1,EndTime) as time) ) select cte.StudentID , StartTime , EndTime , Allocatable = datediff(minute,StartTime,EndTime) , Allocated = count(*) , Unallocated = datediff(minute,StartTime,EndTime) - count(*) from cte join ( select StudentID, ActivityDate, n_time from #student_time st cross join #tally_time tt where RecordType 'A' and tt.n_time between st.StartTime and cast(dateadd(minute,-1,EndTime) as time) group by StudentID, ActivityDate, n_time ) a on cte.StudentID = a.StudentID and cte.ActivityDate = a.ActivityDate and cte.n_time = a.n_time group by cte.StudentID , StartTime , EndTime
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

diruser avatar image diruser commented ·
iainrobertson Moved this to comment: Thank you. I used 15 min as an example, it can be down to the minute in production. This is beyond me, so I will review this and try to adopt to our data. if I understand you create a table and load every 1 min into it, then remove every one min form the other records that overlap?
0 Likes 0 ·
diruser avatar image diruser commented ·
thank you. My example used 15 min increments but live data will be down to the min. if I understand you create a temp table with all minutes, then subtract the minutes from any subtracting records?
0 Likes 0 ·
iainrobertson avatar image iainrobertson commented ·
That's right. I create a set of minutes that are "owned" by the A record. Then I compare the distinct set of minutes that are "owned" by the all of other records to this set. Any minutes that appear in both sets are counted as allocated, any minutes that are in set A only are counted as unallocated. Think of it as a bit like a Venn diagram.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.