时间戳

首页 > 解决方案 > SQL 将 Stretch Date 切割成不同的时间段

sql - SQL 将 Stretch Date 切割成不同的时间段

问题描述

我在一张桌子上有一大段日期，在另一张桌子上有一些日期要切。

有了这个，我需要得到另一张桌子，里面有其他日期切割的大桌子内的所有伸展。

在此处输入图像描述

我暂时有这个：

DECLARE @UTCSTARTDATE DATETIME='2020-01-20 00:00:00.00'
DECLARE @UTCENDDATE DATETIME='2020-01-20 04:00:00.00'

DECLARE @T1 TABLE
(
    StartTime DATETIME,
    EndTime DATETIME,
    table_id int
)

DECLARE @T2 TABLE
(
    CutTime DATETIME,
    table2_id int
)


INSERT INTO @T1 SELECT @UTCSTARTDATE,@UTCENDDATE,1

INSERT INTO @T2 SELECT myCut,myID FROM (VALUES('2020-01-20 01:00:00.00',1),('2020-01-20 02:30:00.00',1),('2020-01-20 03:00:00.00',2))t2(myCut,myID)


SELECT * FROM @T1

SELECT * FROM @T2 order by CutTime ASC



DECLARE @STRETCHHOURSTABLE TABLE
    (
        startDate               DATETIME,
        endDate                 DATETIME

    );

    ;WITH DATESPLITTER AS
    (
            SELECT 
                StartTime AS stretchStartDate, 
                (SELECT TOP(1) CutTime FROM @T2 where CutTime < EndTime AND table_id = table2_id order by CutTime ASC) AS stretchEndDate,
                EndTime AS RealEndTime,
                table_id AS RealID
            FROM @T1
        UNION ALL 
            SELECT 
                stretchEndDate,
                (SELECT TOP(1) CutTime FROM @T2 where CutTime > stretchEndDate AND CutTime < RealEndTime AND table2_id = RealID order by CutTime ASC) AS stretchEndDate,
                RealEndTime,
                RealID
            FROM DATESPLITTER
            WHERE stretchEndDate < @UTCENDDATE
    )
        INSERT INTO @STRETCHHOURSTABLE (startDate, endDate)
        SELECT 
            stretchStartDate,
            CASE    
                WHEN @UTCENDDATE < stretchEndDate THEN @UTCENDDATE
                ELSE stretchEndDate
            END 
        FROM DATESPLITTER

SELECT * FROM @STRETCHHOURSTABLE

标签： sqlsql-servertsqldate

解决方案

这似乎基本上是lead()在union all：

select dt, lead(dt) over (order by dt)
from ((select v.dt
       from @t1 t1 cross apply
            (values (t1.startTime), (t1.endTime)) v(dt)
      ) union all
      (select t2.cuttime
       from @t2 t2
      )
     ) t

这是一个 db<>fiddle。

推荐阅读