Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Room occupancy per day given a date range Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 7:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 6:19 AM
Points: 8, Visits: 50
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
Post #1373780
Posted Wednesday, October 17, 2012 7:36 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 8:57 AM
Points: 3,138, Visits: 1,060
DATEDIFF ( datepart , startdate , enddate )




Post #1373785
Posted Wednesday, October 17, 2012 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 6:19 AM
Points: 8, Visits: 50
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

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
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
PatientID 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.000000
1 2012-10-15 10:00:00 2012-10-17 08:59:00 290 2012-10-16 00:00:00.000 1440.000000
1 2012-10-15 10:00:00 2012-10-17 08:59:00 291 2012-10-17 00:00:00.000 539.000000
2 2012-10-11 08:00:00 2012-10-16 12:59:00 285 2012-10-11 00:00:00.000 960.000000
2 2012-10-11 08:00:00 2012-10-16 12:59:00 286 2012-10-12 00:00:00.000 1440.000000
2 2012-10-11 08:00:00 2012-10-16 12:59:00 287 2012-10-13 00:00:00.000 1440.000000
2 2012-10-11 08:00:00 2012-10-16 12:59:00 288 2012-10-14 00:00:00.000 1440.000000
2 2012-10-11 08:00:00 2012-10-16 12:59:00 289 2012-10-15 00:00:00.000 1440.000000
2 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
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 6:19 AM
Points: 8, Visits: 50
Wow, Ray M. That looks very promising. Thanks!!!!
Post #1373907
Posted Wednesday, October 17, 2012 10:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:28 PM
Points: 1,945, Visits: 2,900
Look up the use of a Report Period table.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1373924
Posted Friday, October 19, 2012 5:04 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 1:27 AM
Points: 115, Visits: 143
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

Post #1374736
Posted Saturday, October 20, 2012 1:00 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:14 AM
Points: 3,618, Visits: 5,254
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 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!
Post #1375086
Posted Saturday, October 20, 2012 4:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 6:19 AM
Points: 8, Visits: 50
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

Post #1375096
Posted Tuesday, October 23, 2012 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 6:19 AM
Points: 8, Visits: 50
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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse