Having problem with Select using month/hour

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  

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;

db<>fiddle