October 17, 2012 at 7:26 am
given data like this:
PatientID, StartDate, EndDate
1, 2012-10-15 10:00:00, 2012-10-17 08:59:00
Any suggestions on how to turn that into daily data like this:
PatientID, Date, MinutsInRoom
1, 2012-10-15, 839
1, 2012-10-16, 1440
1, 2012-10-17, 539
October 17, 2012 at 7:36 am
DATEDIFF ( datepart , startdate , enddate )
October 17, 2012 at 7:38 am
Yes, but I have to do some kind of loop to build those three records, one record for each day.
October 17, 2012 at 9:26 am
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 Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY n) AS num
FROM Nbrs ) t
WHERE 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)
END
FROM (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) t
INNER JOIN @Dates d ON d.Date BETWEEN CAST(t.StartDate AS DATE) AND t.EndDate
ORDER BY PatientID, StartDate
Results
PatientIDStartDateEndDateIDDate(No column name)
12012-10-15 10:00:002012-10-17 08:59:002892012-10-15 00:00:00.000840.000000
12012-10-15 10:00:002012-10-17 08:59:002902012-10-16 00:00:00.0001440.000000
12012-10-15 10:00:002012-10-17 08:59:002912012-10-17 00:00:00.000539.000000
22012-10-11 08:00:002012-10-16 12:59:002852012-10-11 00:00:00.000960.000000
22012-10-11 08:00:002012-10-16 12:59:002862012-10-12 00:00:00.0001440.000000
22012-10-11 08:00:002012-10-16 12:59:002872012-10-13 00:00:00.0001440.000000
22012-10-11 08:00:002012-10-16 12:59:002882012-10-14 00:00:00.0001440.000000
22012-10-11 08:00:002012-10-16 12:59:002892012-10-15 00:00:00.0001440.000000
22012-10-11 08:00:002012-10-16 12:59:002902012-10-16 00:00:00.000779.000000
October 17, 2012 at 9:39 am
Wow, Ray M. That looks very promising. Thanks!!!!
October 19, 2012 at 5:05 am
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.html
Enjoy!
Eamon:-)
October 20, 2012 at 1:00 am
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 @T
SELECT 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 END
FROM @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
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 20, 2012 at 4:55 am
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 @T
SELECT 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
END
FROM @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
October 23, 2012 at 8:55 am
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.
October 23, 2012 at 11:21 am
!! WOW !!!
and it's simpler too !:-)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply