CODEWITHSUNDEEP
sqlt-sql
sa102
sa102

Reputation: 59

SQL Showing Every Hour of Every Day

I wrote the below code to break out my data that shows patient arrival and departure by day, into patient census by hour of every day.

The code works but for every date, instead of adding one hour each for the hours 0-23, it adds a second line for 0, so it breaks every day into 25 lines instead of 24. I'm pretty sure the problem is somewhere in the Cross Apply below, but I included the rest of the code for your reference.

I'd really appreciate any help you can give. Also, if you have any tips on how to post code in here and have it look more normal, let me know. Thank you! 

--Create my temporary table 
SELECT *
INTO #Temporary
FROM dbo.Census
WHERE YEAR(startdatetime) >= 2018
ORDER BY
    startdatetime
    ,pt_id

--Use the Cross Apply to split out every day into every hour 
SELECT
    Date = CAST(D AS DATE)
    ,Hour = DATEPART(HOUR, D)
    ,pt_id
    ,cendate
    ,locationid
    ,[room-bed]
    ,startdatetime
    ,enddatetime
    ,minutes
    ,DayOfWeek
    ,WeekInt
    ,MyStartMinutes = 0
    ,MyEndMinutes = 0
INTO #Temporary2
FROM #Temporary A
CROSS APPLY
(
    SELECT TOP ( ABS(DATEDIFF(HOUR, A.startdatetime, A.enddatetime) + 1))
        D = DATEADD(HOUR, -1 + ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )), A.startdatetime)
    FROM master..spt_values n1
        ,master..spt_values n2
) B

--Update values for MyStartMinutes and MyEndMinutes 
UPDATE #Temporary2
SET MyStartMinutes = CASE WHEN ( DATEPART(HOUR, startdatetime) = Hour )
                              THEN DATEPART(mi, enddatetime)
                         ELSE 0 END

UPDATE #Temporary2
SET MyEndMinutes = CASE WHEN ( DATEPART(HOUR, enddatetime) = Hour )
                            AND DATEDIFF(DAY, enddatetime, cendate) = 0
                            THEN DATEPART(mi, enddatetime)
                       ELSE 0 END

--Update values of startdatetime and enddatetime 
UPDATE #Temporary2
SET startdatetime = DATEADD(HOUR, Hour, DATEADD(MINUTE, MyStartMinutes, CAST(CAST(startdatetime AS DATE) AS DATETIME)))

UPDATE #Temporary2
SET enddatetime = CASE WHEN ( Hour < 23 )
                           THEN ( DATEADD(HOUR, Hour + 1, DATEADD(MINUTE, MyEndMinutes, CAST(CAST(startdatetime AS DATE) AS DATETIME))))
                      WHEN Hour = 23
                          THEN ( DATEADD(HOUR, 0, DATEADD(MINUTE, MyEndMinutes, CAST(CAST(enddatetime AS DATE) AS DATETIME))))
                      ELSE '' END

--Update Value of Minutes 
UPDATE #Temporary2
SET Minutes = DATEDIFF(mi, startdatetime, enddatetime)

SELECT * 
FROM #Temporary2 
ORDER BY minutes DESC

Here is the sample data from dbo.Census:

org pt_id cendate   location bed    startdate      enddate           minutes DOW 
A   5     1/8/2018    7E     50    1/8/2018 8:00    1/9/2018 0:00    960     Mon 
A   5     1/9/2018    7E     50    1/9/2018 0:00    1/10/2018 0:00   1440    Tue 
A   5     1/10/2018   7E     50    1/10/2018 0:00   1/11/2018 0:00   1440    Wed 
A   5     1/11/2018   7E     50    1/11/2018 0:00   1/11/2018 14:00  840     Thu 
A   1     10/17/2016  ED     10    10/17/2016 1:05  10/17/2016 10:21 556     Mon 
A   2     5/10/2017   4L     20    5/10/2017 15:09  5/11/2017 0:00   531     Wed 
A   3     5/14/2017   4L     30    5/14/2017 0:00   5/14/2017 8:12   492     Sun 
A   4     6/3/2017    5C     40    6/3/2017 0:00    6/4/2017 0:00    1440    Sat

Upvotes: 0

Views: 322

Answers (1)

digital.aaron
digital.aaron

Reputation: 5707

I think you're correct that your CROSS APPLY is the culprit here. After testing your code on my own sample data, I found that if there were separate records in dbo.Census that had overlapping days between their startdates and enddates, those dates and hours would get duplicated, depending on how many records and how many days they share.

So what I did was add the PK from dbo.Census into the CROSS APPLY, and then used that id column in the subquery to filter the results to only those where the ids matched. Here's the section of code I changed:

SELECT
    Date = CAST(D AS DATE)
    ,Hour = DATEPART(HOUR, D)
    ,A.pt_id
    ,cendate
    ,locationid
    ,[room-bed]
    ,startdatetime
    ,enddatetime
    ,minutes
    ,DayOfWeek
    ,WeekInt
    ,MyStartMinutes = 0
    ,MyEndMinutes = 0
INTO #Temporary2
FROM #Temporary A
CROSS APPLY
(
    SELECT TOP ( ABS(DATEDIFF(HOUR, A.startdatetime, A.enddatetime) + 1))
        D = DATEADD(HOUR, -1 + ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )), A.startdatetime)
        ,A.pt_id
    FROM master..spt_values n1
        ,master..spt_values n2
) B
WHERE A.pt_id = B.pt_id

I made the assumption that pt_id is the primary key of dbo.Census. If that's not the case, you would just replace pt_id with the PK from dbo.Census.

Upvotes: 1

Related Questions