sql-server - 如果结束日期在一个间隔处为空，我如何合并它们之间的日期差异

Question

嗨，请帮我解决这个问题

此代码将合并所有非空日期它们之间的差异一天一次时期

---------------------------------------------------------------------------
-- Data
---------------------------------------------------------------------------

DECLARE @Test TABLE
(
    startDate DATE,
    ENDDate DATE,
    EmpName NVARCHAR(MAX),
    EmpID INT
);

INSERT INTO @Test
VALUES
( '2001-01-10', '2001-01-20' , 'yanal', 1 )

INSERT INTO @Test
VALUES
( '2001-01-21', '2001-02-10' , 'yanal', 1 )


INSERT INTO @Test
VALUES
( '2001-02-21', '2001-03-10' , 'abd', 2 )

INSERT INTO @Test
VALUES
( '2001-03-11', '2001-03-20' , 'abd', 2  )


INSERT INTO @Test
VALUES
( '2001-04-11', '2001-05-20' , 'raed', 3 )

INSERT INTO @Test
VALUES
( '2001-05-21', '2001-06-10' , 'raed', 3 )

INSERT INTO @Test
VALUES
( '2001-6-11',null, 'raed', 3 )

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

;WITH CTE
AS (SELECT p1.StartDate,
           p1.EndDate,
           p1.EmpName,
           p1.EmpID
    FROM @Test p1
        LEFT JOIN @Test p2
            ON p1.StartDate = DATEADD(DAY, 1, p2.EndDate) 
               AND p1.EmpID = p2.EmpID
    WHERE p1.EndDate IS NULL
          OR p2.StartDate IS NULL
    UNION ALL
    SELECT p1.StartDate,
           p2.EndDate,
           p1.EmpName,
           p1.EmpID
    FROM CTE p1
        INNER JOIN @Test p2
            ON p1.EndDate = DATEADD(DAY, -1, p2.StartDate)
               AND p1.EmpID = p2.EmpID)


SELECT StartDate,
       MAX(EndDate) AS EndDate,
       EmpName
FROM CTE
GROUP BY StartDate,
         EmpName;    

输出查询

StartDate   EndDate      EmpName
2001-02-21  2001-03-20    abd
2001-04-11  2001-06-10    raed
2001-06-11  NULL          raed
2001-01-10  2001-02-10    yanal

我需要将一个期间员工合并到一个期间

成为输出

StartDate   EndDate      EmpName
2001-02-21  2001-03-20    abd
2001-04-11  NULL          raed
2001-01-10  2001-02-10    yanal

谢谢你

标签： sql-server

Answer 1

这不是一个非常优雅的解决方案，但它确实有效。我添加了一个额外的 CTE。

DECLARE @Test TABLE
(
    startDate DATE,
    ENDDate DATE,
    EmpName NVARCHAR(MAX),
    EmpID INT
);

INSERT INTO @Test
VALUES
( '2001-01-10', '2001-01-20' , 'yanal', 1 )

INSERT INTO @Test
VALUES
( '2001-01-21', '2001-02-10' , 'yanal', 1 )


INSERT INTO @Test
VALUES
( '2001-02-21', '2001-03-10' , 'abd', 2 )

INSERT INTO @Test
VALUES
( '2001-03-11', '2001-03-20' , 'abd', 2  )


INSERT INTO @Test
VALUES
( '2001-04-11', '2001-05-20' , 'raed', 3 )

INSERT INTO @Test
VALUES
( '2001-05-21', '2001-06-10' , 'raed', 3 )

INSERT INTO @Test
VALUES
( '2001-6-11',null, 'raed', 3 )

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

;WITH CTE
AS (SELECT p1.StartDate,
           p1.EndDate,
           p1.EmpName,
           p1.EmpID
    FROM @Test p1
        LEFT JOIN @Test p2
            ON p1.StartDate = DATEADD(DAY, 1, p2.EndDate) 
               AND p1.EmpID = p2.EmpID
    WHERE p1.EndDate IS NULL
          OR p2.StartDate IS NULL
    UNION ALL
    SELECT p1.StartDate,
           p2.EndDate,
           p1.EmpName,
           p1.EmpID
    FROM CTE p1
        INNER JOIN @Test p2
            ON p1.EndDate = DATEADD(DAY, -1, p2.StartDate)
               AND p1.EmpID = p2.EmpID)

, CTE_2 AS 
(
    SELECT StartDate,
         MAX(CASE WHEN EndDate IS NULL THEN '29991231' ELSE ENDDate END) AS EndDate,
         EmpName
    FROM CTE
    GROUP BY StartDate,
           EmpName
)

SELECT EmpName, 
      MIN (StartDate) AS StartDate,
      CASE WHEN EndDate = '29991231' THEN NULL ELSE EndDate END AS EndDate
FROM CTE_2 
GROUP BY EmpName, CASE WHEN EndDate = '29991231' THEN NULL ELSE EndDate END
ORDER BY EmpName