比较历史行（LAG 行）并将更改的值合并到单列

debugcn 发表于 Dev

哈尔萨尔

比较历史行（基于 ResultChngDt 的 LAG 行）并将更改的列值合并为单列。寻求帮助编写优雅/高效的 SQL Server 2016 TSQL 代码（无游标）。

我有一个结构和数据如下的表：

+----+-------+--------------+---------------+--------+--------+--------------+
| ID | RepID | CollctedDate | CompletedDate | Result | Tcode  | ResultChngDt |
+----+-------+--------------+---------------+--------+--------+--------------+
| 1  | 101   | 11/20/2017   | 12/13/2017    |        | L-2190 | 12/13/2017   |
| 1  | 101   | 11/22/2017   | 12/15/2017    | POS    | L-Afb  | 1/5/2018     |
| 1  | 102   | 11/22/2017   | 12/15/2017    |        | L-2191 | 12/15/2017   |
| 1  | 102   | 11/22/2017   | 12/15/2017    | POS    | L-2192 | 12/31/2017   |
+----+-------+--------------+---------------+--------+--------+--------------+

我需要生成一个报告/结果如下：

+----+-------+---------------------------+--------------------------+--+
| ID | RepID | Previous                  | Current                  |  |
+----+-------+---------------------------+--------------------------+--+
| 1  | 101   | CollctedDate:11/20/2017   | CollctedDate:11/22/2017  |  |
|    |       | CompletedDate:12/13/2017  | CompletedDate:12/15/2017 |  |
|    |       | Result:                   | Result:POS               |  |
|    |       | Tcode:L-2190              | Tcode:L-Afb              |  |
| 1  | 102   | CollctedDate:11/22/2017   | CollctedDate:11/22/2017  |  |
|    |       | CompletedDate:12/15/2017  | CompletedDate:12/15/2017 |  |
|    |       | Result:                   | Result:POS               |  |
|    |       | Tcode:L-2191              | Tcode:L-2192             |  |
+----+-------+---------------------------+--------------------------+--+

CREATE TABLE [dbo].[Table1]
    (
        [ID]            INT         NULL,
        [RepID]         INT         NULL,
        [CollctedDate]  DATETIME    NULL,
        [CompletedDate] DATETIME    NULL,
        [Result]        VARCHAR(3)  NULL,
        [Tcode]         VARCHAR(10) NULL,
        [ResultChngDt]  DATETIME    NULL
    ) ON [PRIMARY];
GO

INSERT INTO [dbo].[Table1] ([ID], [RepID], [CollctedDate], [CompletedDate], [Result], [Tcode], [ResultChngDt])
 VALUES (1, 101, N'11/20/2017', N'12/13/2017', N'', N'L-2190', N'12/13/2017') 
, (1, 101, N'11/22/2017', N'12/15/2017', N'POS', N'L-Afb', N'1/5/2018') 
, (1, 102, N'11/22/2017', N'12/15/2017', N'', N'L-2191', N'12/15/2017') 
, (1, 102, N'11/22/2017', N'12/15/2017', N'POS', N'L-2192', N'12/31/2017')

光环

这是我对您的问题的查询：

    WITH cte_LEADLAG AS(
    SELECT ID,
           RepID,
           CollctedDate,
           CompletedDate,
           Result,
           Tcode,
           ResultChngDt,
           CONCAT('CollectedDate:',CAST(CollctedDate AS DATETIME2), ' CompletedDate:', CAST(CompletedDate AS DATETIME2), ' Result:', Result, ' Tcode', Tcode) AS dates,
           LAG(CollctedDate) OVER(PARTITION BY RepID ORDER BY CollctedDate) AS 'LAGCollectedDate'  ,
           lead(CollctedDate) OVER(PARTITION BY RepID ORDER BY CollctedDate) AS 'LEADCollectedDate',
           LAG(CompletedDate) OVER(PARTITION BY RepID ORDER BY CompletedDate) AS 'LAGCompDate'  ,
           lead(CompletedDate) OVER(PARTITION BY RepID ORDER BY CompletedDate) AS 'LEADcompDate' ,
           LEAD(Result) OVER(PARTITION BY RepID ORDER BY CompletedDate) AS 'LEADResult' ,
           LEAD(Tcode) OVER(PARTITION BY RepID ORDER BY CompletedDate) AS 'LEADTcode'    
    FROM #temp
    ),
     cte_FINAL AS(
    SELECT distinct ID,
                    RepID,
                    CASE WHEN cte.LAGCollectedDate IS NULL THEN CONCAT('CollectedDate:',CAST(CollctedDate AS DATETIME2), ' CompletedDate:', CAST(CompletedDate AS DATETIME2), ' Result:', Result, ' Tcode', Tcode) end AS 'Previous',
                    CASE WHEN cte.LEADCollectedDate IS not NULL THEN  CONCAT('CollectedDate:',CAST(cte.LEADCollectedDate AS DATETIME2), ' CompletedDate:', CAST(LEADcompDate AS DATETIME2), ' Result:', cte.LEADResult, ' Tcode', cte.LEADTcode) end AS 'Current'
    FROM cte_LEADLAG AS cte
    WHERE cte.LEADCollectedDate IN (SELECT MAX(LEADCollectedDate) FROM cte_LEADLAG WHERE cte_LEADLAG.RepID = cte.RepID))
)
    SELECT *
    FROM cte_FINAL;

结果：