The database is from a Hospital and the problem is when the month changes, like this: The patient gets to the UTI on the date 30/03/2020 6AM and left on 02/04/2020 11AM so theoretically they spend (18+24+24+11) 89 hours in total butIput this on Business Intelligence(BI) the BI thinks that they spent 77hours on the UTI only on March and didn’t show anything for April
So i have to divide like 30/03/2020 6AM end on 31/03/2020 23:59PM = 42h, and 01/04/2020 start on 00AM and end on 02/04/2020 11PM = 47h
So they achieve 77 hours but it is divided between March and April.
My goal is to divide this in SQL Server with select or something, i was reading about DATEPART
/DATENAME
but unsuccessfully.
SELECT S.STR_NOME , LOC.LOC_NOME , LTO.LTO_TIPO , LTO.LTO_PAC_REG , PAC.PAC_NOME , MTL.MTL_DESCR --, BLC.BLC_LOC_COD --, BLC.BLC_DTHR_INI --, BLC.BLC_STATUS ,LTO.LTO_DTHR_INI ,LTO.LTO_DTHR_FIM FROM LTO JOIN PAC ON PAC.PAC_REG = LTO.LTO_PAC_REG JOIN LOC ON LOC.LOC_COD = LTO.LTO_LOC_COD LEFT JOIN MTL ON MTL.MTL_COD = LTO.LTO_MTL_COD LEFT JOIN STR S ON S.STR_COD =LOC.LOC_STR --FULL OUTER JOIN BLC ON BLC.BLC_LOC_COD = LOC.LOC_COD AND BLC.BLC_DTHR_FIM = LTO.LTO_DTHR_INI WHERE LTO.LTO_DTHR_INI >= '2020-01-01 00:00:00' AND LOC.LOC_NOME ='ENFERMARIA 112-A ' AND LTO.LTO_PAC_REG = '144755' ORDER BY LTO.LTO_DTHR_INI
Advertisement
Answer
What you need to do is add a row for each month into your data. One method is using an rCTe, however, I prefer to use a Tally as they are significantly faster for larger data sets; though here there’s likely to be little to no performance difference.
I assume 36 rows, 3 years, is enough here. I then use a small inline tally to create a row for each month for the start and end dates, and then a get the difference, in hours, for each month using DATEDIFF
:
CREATE TABLE dbo.YourTable (ID int IDENTITY(1,1), ArrivalTime datetime2(0), DepartureTime datetime2(0)); GO INSERT INTO dbo.YourTable VALUES('2020-03-30T06:00:00','2020-04-02T11:00:00'), ('2020-01-17T17:00:00','2020-10-30T09:00:00'); GO SELECT * FROM dbo.YourTable; GO WITH Tally AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N1(N) CROSS JOIN (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N2(N)), --36 rows, 3 years. Months AS( SELECT YT.ID, YT.ArrivalTime, YT.DepartureTime, T.I, D.MonthStart AS MonthStartD, D.NextMonthStart AS NextMonthStartD, CASE WHEN D.MonthStart < YT.ArrivalTime THEN YT.ArrivalTime ELSE D.MonthStart END AS MonthStart, CASE WHEN YT.DepartureTime > D.NextMonthStart THEN D.NextMonthStart ELSE YT.DepartureTime END AS MonthEnd FROM dbo.YourTable YT JOIN Tally T ON DATEDIFF(MONTH, YT.ArrivalTime,YT.DepartureTime) >= T.I CROSS APPLY (VALUES(DATEADD(MONTH,T.I,DATEFROMPARTS(YEAR(YT.ArrivalTime),MONTH(YT.ArrivalTime),1)), DATEADD(MONTH,T.I+1,DATEFROMPARTS(YEAR(YT.ArrivalTime),MONTH(YT.ArrivalTime),1))))D(MonthStart,NextMonthStart)) SELECT ID, DATEPART(YEAR, MonthStart) AS Year, DATENAME(MONTH, MonthStart) AS Month, DATEDIFF(HOUR,MonthStart,MonthEnd) AS Hours FROM Months ORDER BY ID, DATEPART(YEAR, MonthStart), DATEPART(MONTH, MonthStart); GO DROP TABLE dbo.YourTable;