使用月/小时选择时出现问题

Question

数据库来自一家医院，问题是当月份发生变化时，如下所示:患者在2020年2月3日早上6点到达医院，在2020年2月4日11点离开，因此理论上他们总共在商业智能( BI )上花费(18+24+24+11) 89小时butIput BI认为他们仅在3月份在UTI上花费了77小时，而在4月份没有显示任何内容

所以我必须划分为30/03/2020 6 6AM end to 31/03/2020 23:59 6AM= 42h，01/04/2020开始于00 6AM，结束于02/04/2020 11 6AM=47 h

所以他们达到了77个小时，但它在3月和4月之间分配。

我的目标是在SQL Server中用select或其他的东西来划分它，我读到了关于DATEPART/DATENAME的内容，但没有成功。

​

​

​

​

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 1

您需要做的是在您的数据中为每个月添加一行。一种方法是使用rCTe，但是，我更喜欢使用计数，因为对于更大的数据集，它们的速度要快得多；尽管在这里可能几乎没有性能差异。

我假设36行，3年，在这里就足够了。然后，我使用一个小的内联计数为每个月的开始日期和结束日期创建一行，然后使用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