## Room occupancy per day given a date range

 Author Message dplaut 49149 SSC-Enthusiastic Group: General Forum Members Points: 179 Visits: 89 given data like this:PatientID, StartDate, EndDate1, 2012-10-15 10:00:00, 2012-10-17 08:59:00Any suggestions on how to turn that into daily data like this:PatientID, Date, MinutsInRoom1, 2012-10-15, 8391, 2012-10-16, 14401, 2012-10-17, 539 Chandra Sekhara Vyas Dhara SSCertifiable Group: General Forum Members Points: 7127 Visits: 1149 DATEDIFF ( datepart , startdate , enddate ) dplaut 49149 SSC-Enthusiastic Group: General Forum Members Points: 179 Visits: 89 Yes, but I have to do some kind of loop to build those three records, one record for each day. Ray M SSChampion Group: General Forum Members Points: 11789 Visits: 1076 Consider using a table of dates.I'm not saying this is the best solution, I'm just showing you can do it without using a loop.`DECLARE @Dates TABLE (ID INT IDENTITY, Date DATETIME)-- Credit to Itzik Ben-Gan for the spiffy query to create a table of numbers then converted to dates;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ), Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )INSERT INTO @Dates(Date)SELECT DATEADD(dd, num, CAST('01/01/2012' AS DATETIME) -1) AS DateFROM (SELECT ROW_NUMBER() OVER (ORDER BY n) AS num FROM Nbrs ) tWHERE DATEADD(dd, num, CAST('01/01/2012' AS DATETIME) -1) BETWEEN '01/01/2012' AND '12/31/2012' SELECT *,CASE WHEN t.StartDate > [Date] THEN ROUND((DATEDIFF(ss, StartDate, DATEADD(dd, 1, d.[Date])) / 60.00),2) WHEN d.[Date] = CAST(t.EndDate AS DATE) THEN ROUND((DATEDIFF(ss, d.[Date], t.EndDate) / 60.00),2) WHEN d.[Date] < t.EndDate THEN ROUND((DATEDIFF(ss, d.[Date], DATEADD(dd, 1, d.[Date])) / 60.00),2) ENDFROM (SELECT 1 AS PatientID, '2012-10-15 10:00:00' AS StartDate, '2012-10-17 08:59:00' AS EndDate UNION SELECT 2 AS PatientID, '2012-10-11 08:00:00' AS StartDate, '2012-10-16 12:59:00' AS EndDate) tINNER JOIN @Dates d ON d.Date BETWEEN CAST(t.StartDate AS DATE) AND t.EndDateORDER BY PatientID, StartDate`ResultsPatientID StartDate EndDate ID Date (No column name)1 2012-10-15 10:00:00 2012-10-17 08:59:00 289 2012-10-15 00:00:00.000 840.0000001 2012-10-15 10:00:00 2012-10-17 08:59:00 290 2012-10-16 00:00:00.000 1440.0000001 2012-10-15 10:00:00 2012-10-17 08:59:00 291 2012-10-17 00:00:00.000 539.0000002 2012-10-11 08:00:00 2012-10-16 12:59:00 285 2012-10-11 00:00:00.000 960.0000002 2012-10-11 08:00:00 2012-10-16 12:59:00 286 2012-10-12 00:00:00.000 1440.0000002 2012-10-11 08:00:00 2012-10-16 12:59:00 287 2012-10-13 00:00:00.000 1440.0000002 2012-10-11 08:00:00 2012-10-16 12:59:00 288 2012-10-14 00:00:00.000 1440.0000002 2012-10-11 08:00:00 2012-10-16 12:59:00 289 2012-10-15 00:00:00.000 1440.0000002 2012-10-11 08:00:00 2012-10-16 12:59:00 290 2012-10-16 00:00:00.000 779.000000 dplaut 49149 SSC-Enthusiastic Group: General Forum Members Points: 179 Visits: 89 Wow, Ray M. That looks very promising. Thanks!!!! EamonSQL Ten Centuries Group: General Forum Members Points: 1129 Visits: 196 Hi,this is an easy one that you can do with an exotic join in conjunction with an addition calendar table.Yesterday I posted a short case study which illustrates what you're looking to solve.http://londondayoff.blogspot.co.uk/2012/10/thepower-of-exotic-joins-and-why-having.htmlEnjoy!Eamon:-) dwain.c SSC-Forever Group: General Forum Members Points: 44105 Visits: 6431 I believe that 10AM until the end of the day should be 840 minutes and not 839. If that is the case, I propose this easy-on-the-eyes solution:`DECLARE @T TABLE (PatientID INT, StartDate DATETIME, EndDate DATETIME)INSERT INTO @TSELECT 1, '2012-10-15 10:00:00', '2012-10-17 08:59:00'UNION ALL SELECT 2, '2012-10-11 08:00:00', '2012-10-16 12:59:00';WITH Tally(n) AS ( SELECT TOP (SELECT 1+MAX(DATEDIFF(dd, StartDate, EndDate)) FROM @T) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.all_columns a CROSS JOIN sys.all_columns b)SELECT PatientID, StartDate, EndDate, CAST(Date AS DATE) ,Minutes=CASE WHEN DATEADD(dd, 0, DATEDIFF(dd, 0, StartDate)) = Date THEN DATEDIFF(minute, StartDate, Date+1) WHEN DATEADD(dd, 0, DATEDIFF(dd, 0, EndDate)) = Date THEN DATEDIFF(minute, Date, EndDate) ELSE 1440 ENDFROM @TCROSS APPLY ( SELECT DATEADD(dd, n, DATEADD(dd, 0, DATEDIFF(dd, 0, StartDate))) FROM Tally WHERE n BETWEEN 0 AND DATEDIFF(dd, StartDate, EndDate)) b (Date)ORDER BY PatientID, Date` My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables dplaut 49149 SSC-Enthusiastic Group: General Forum Members Points: 179 Visits: 89 Well, since you said "easy on the eyes" i reformatted this. I'll take a look at it next week.`DECLARE @T TABLE ( PatientID INT, StartDate DATETIME, EndDate DATETIME)INSERT INTO @TSELECT 1, '2012-10-15 10:00:00', '2012-10-17 08:59:00'UNION ALLSELECT 2, '2012-10-11 08:00:00', '2012-10-16 12:59:00';WITH Tally(n) AS (SELECT TOP (SELECT 1+Max(Datediff(dd, StartDate, EndDate)) FROM @T) Row_number() OVER (ORDER BY (SELECT NULL)) - 1 FROM sys.all_columns a CROSS JOIN sys.all_columns b)SELECT PatientID, StartDate, EndDate, Cast(Date AS DATE), Minutes=CASE WHEN Dateadd(dd, 0, Datediff(dd, 0, StartDate)) = Date THEN Datediff(minute, StartDate, Date + 1) WHEN Dateadd(dd, 0, Datediff(dd, 0, EndDate)) = Date THEN Datediff(minute, Date, EndDate) ELSE 1440 ENDFROM @T CROSS APPLY (SELECT Dateadd(dd, n, Dateadd(dd, 0, Datediff(dd, 0, StartDate))) FROM Tally WHERE n BETWEEN 0 AND Datediff(dd, StartDate, EndDate)) b (Date)ORDER BY PatientID, Date ` dplaut 49149 SSC-Enthusiastic Group: General Forum Members Points: 179 Visits: 89 The method with the table of dates runs four times faster. This based on repeated tests with 1.8 million rows in the source table and putting out 6.9 million rows in the result.