代码之家 › 专栏 › 技术社区 › TSDrake

SQL将拉伸日期剪切成各种长度

date tsql sql-server sql

0

TSDrake · 技术社区 · 6 年前

我在一张桌子上放了一大段日期,在另一张桌子上剪了一些日期。

我现在有这个:

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

0 回复 | 直到 6 年前

1

0

Gordon Linoff 6 年前

基本上是这样 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

Here

2

-1

Dale K 6 年前

试试这个,我做了些改变:

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 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);

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 cuttime from (SELECT row_number() over(partition by table2_id order by CutTime ASC) rn, CutTime FROM @T2 where CutTime < EndTime AND table_id = table2_id)a
                  where a.rn = 1) AS stretchEndDate,
                EndTime AS RealEndTime,
                table_id AS RealID
            FROM @T1
        UNION ALL 
            SELECT 
                stretchEndDate,
                ISNULL((select cuttime from (SELECT row_number() over(partition by table2_id order by CutTime ASC) rn, CutTime FROM @T2 where CutTime > stretchEndDate AND CutTime < RealEndTime AND table2_id = RealID)a
                  where a.rn = 1), RealEndTime) 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 as sommm
FROM DATESPLITTER

SELECT * FROM @STRETCHHOURSTABLE