The scenario is users specify when they are available, these specified times can overlap each other. I'm trying to get the total time they are available for. Example with SQL Fiddle:
--Available--
ID userID availStart availEnd
1 456 '2012-11-19 16:00' '2012-11-19 17:00'
2 456 '2012-11-19 16:00' '2012-11-19 16:50'
3 456 '2012-11-19 18:00' '2012-11-19 18:30'
4 456 '2012-11-19 17:30' '2012-11-19 18:10'
5 456 '2012-11-19 16:00' '2012-11-19 17:10'
6 456 '2012-11-19 16:00' '2012-11-19 16:50'
The output should be 130 minutes:
1: 60
2: 0 as falls inside 1
3: 30
4: 30 as the last 10 mins is covered by 3
5: 10 as first 60 mins is covered by 1
6: 0 as falls inside 1
I can get the total overlapping minutes, however this is more than the SUM of the available minutes:
SQL Fiddle
Any ideas how I can achieve this?
EDIT 21st Nov 12: Thanks so far for everyone's solutions - in a way I'm happy to see this wasn't an 'easy' query to write.
EDIT 23rd Nov 12: This is all great work. Internally here we're thinking it might be best to ensure users cannot enter overlapping times (eg forcing them to amend an existing entry)!
Gordon Linoff has a CTE based answer
I've done some performance analysis on all the working algorithms Blank values mean it took too long. This is tested on a single Core i7 X920 @2GHz chip, backed by a couple of SSDs. The only index created was a cluster on UserID, AvailStart. If you think you can improve any of the performance, let me know.
This CTE version was worse than linear, SQL Server can't do the RN = RN + 1 join in an efficient way. I rectified this with a hybrid approach below, where I save and index the first CTE into a table variable. This still takes ten times as much IO as the cursor based approach.
With OrderedRanges as (
Select
Row_Number() Over (Partition By UserID Order By AvailStart) AS RN,
AvailStart,
AvailEnd
From
dbo.Available
Where
UserID = 456
),
AccumulateMinutes (RN, Accum, CurStart, CurEnd) as (
Select
RN, 0, AvailStart, AvailEnd
From
OrderedRanges
Where
RN = 1
Union All
Select
o.RN,
a.Accum + Case When o.AvailStart <= a.CurEnd Then
0
Else
DateDiff(Minute, a.CurStart, a.CurEnd)
End,
Case When o.AvailStart <= a.CurEnd Then
a.CurStart
Else
o.AvailStart
End,
Case When o.AvailStart <= a.CurEnd Then
Case When a.CurEnd > o.AvailEnd Then a.CurEnd Else o.AvailEnd End
Else
o.AvailEnd
End
From
AccumulateMinutes a
Inner Join
OrderedRanges o On
a.RN = o.RN - 1
)
Select Max(Accum + datediff(Minute, CurStart, CurEnd)) From AccumulateMinutes
http://sqlfiddle.com/#!6/ac021/2
After doing some performance analysis, here's a hybrid CTE/table variable version that performs better than anything except the cursor based approach
Create Function dbo.AvailMinutesHybrid(@UserID int) Returns Int As
Begin
Declare @UserRanges Table (
RN int not null primary key,
AvailStart datetime,
AvailEnd datetime
)
Declare @Ret int = Null
;With OrderedRanges as (
Select
Row_Number() Over (Partition By UserID Order By AvailStart) AS RN,
AvailStart,
AvailEnd
From
dbo.Available
Where
UserID = @UserID
)
Insert Into @UserRanges Select * From OrderedRanges
;With AccumulateMinutes (RN,Accum, CurStart, CurEnd) as (
Select
RN, 0, AvailStart, AvailEnd
From
@UserRanges
Where
RN = 1
Union All
Select
o.RN,
a.Accum + Case When o.AvailStart <= a.CurEnd Then
0
Else
DateDiff(Minute, a.CurStart, a.CurEnd)
End,
Case When o.AvailStart <= a.CurEnd Then
a.CurStart
Else
o.AvailStart
End,
Case When o.AvailStart <= a.CurEnd Then
Case When a.CurEnd > o.AvailEnd Then a.CurEnd Else o.AvailEnd End
Else
o.AvailEnd
End
From
AccumulateMinutes a
Inner Join
@UserRanges o On
a.RN + 1 = o.RN
)
Select
@Ret = Max(Accum + datediff(Minute, CurStart, CurEnd))
From
AccumulateMinutes
Option
(MaxRecursion 0)
Return @Ret
End
http://sqlfiddle.com/#!6/bfd94
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With