Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Room occupancy per day given a date range Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, October 17, 2012 7:26 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, November 29, 2016 7:24 AM Points: 21, 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
Post #1373780
 Posted Wednesday, October 17, 2012 7:36 AM
 Hall of Fame Group: General Forum Members Last Login: Sunday, January 31, 2016 3:05 AM Points: 3,963, Visits: 1,149
 DATEDIFF ( datepart , startdate , enddate )
Post #1373785
 Posted Wednesday, October 17, 2012 7:38 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, November 29, 2016 7:24 AM Points: 21, Visits: 89
 Yes, but I have to do some kind of loop to build those three records, one record for each day.
Post #1373789
 Posted Wednesday, October 17, 2012 9:26 AM
 UDP Broadcaster Group: General Forum Members Last Login: Tuesday, August 16, 2016 3:59 PM Points: 1,487, Visits: 1,076
 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
Post #1373897
 Posted Wednesday, October 17, 2012 9:39 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, November 29, 2016 7:24 AM Points: 21, Visits: 89
 Wow, Ray M. That looks very promising. Thanks!!!!
Post #1373907
 Posted Friday, October 19, 2012 5:04 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, November 3, 2016 5:18 AM Points: 122, Visits: 179
 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
Post #1374736
 Posted Saturday, October 20, 2012 1:00 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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
Post #1375086
 Posted Saturday, October 20, 2012 4:55 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, November 29, 2016 7:24 AM Points: 21, 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 `
Post #1375096
 Posted Tuesday, October 23, 2012 8:55 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, November 29, 2016 7:24 AM Points: 21, 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.
Post #1376109
 Posted Tuesday, October 23, 2012 11:21 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, November 3, 2016 5:18 AM Points: 122, Visits: 179
 !! WOW !!!and it's simpler too !
Post #1376181

 Permissions