时间戳

首页 > 解决方案 > 如何缓冲子组实际未更改的所有更改？

sql - 如何缓冲子组实际未更改的所有更改？

问题描述

一个简单的概念，但我该如何编码呢？

我有一张如下表

CREATE TABLE #TEMPCASE (
    [Rank] [numeric](38, 0) NULL,
    [Case ID] [nvarchar](255) NULL,
    [Date From] [datetime] NULL,
    [Date To] [datetime] NULL,
    [Status] [numeric](38, 0) NULL,
    [Text] [nvarchar](255) NULL
)


INSERT INTO #TEMPCASE ([Rank], [Case ID], [Date From], [Date To], [Status], [Text])
VALUES 
(1, 'MI1181628', '2020-01-08 00:00:00.000', '2020-02-26 00:00:00.000', 25, 'In progress'),
(2, 'MI1181628', '2020-02-27 00:00:00.000', '2020-03-06 00:00:00.000', 25, 'In progress'),
(3, 'MI1181628', '2020-03-07 00:00:00.000', '2020-05-06 00:00:00.000', 25, 'In progress'),
(4, 'MI1181628', '2020-05-07 00:00:00.000', '2020-06-09 00:00:00.000', 30, 'Awaiting'),
(5, 'MI1181628', '2020-06-10 00:00:00.000', '2020-07-10 00:00:00.000', 25, 'In progress'),
(6, 'MI1181628', '2020-07-11 00:00:00.000', '2020-08-09 00:00:00.000', 25, 'In progress'),
(7, 'MI1181628', '2020-08-10 00:00:00.000', '2020-08-11 00:00:00.000', 40,  'Reviewed'),
(8, 'MI1181628', '2020-08-12 00:00:00.000', '2020-08-30 00:00:00.000', 43,  'Ready for invoicing'),
(9, 'MI1181628', '2020-08-31 00:00:00.000', '2020-09-05 00:00:00.000', 50, 'Closed'),
(10, 'MI1181628', '2020-09-06 00:00:00.000', '2020-10-03 00:00:00.000', 50, 'Closed'),
(11, 'MI1181628', '2020-10-04 00:00:00.000', '2020-10-09 00:00:00.000', 50, 'Closed')

而我想要实现的是缓冲状态实际上没有改变的所有变化。所以想要的输出应该是这样的。 输出

目前，这是我一直在尝试的方法，但正如您所知，它效果不佳。现在陷入困境，请发送您的帮助...

SELECT
       RANK() OVER (PARTITION BY [Case ID] ORDER BY [Date From] ASC) AS [Rank],
       [Case ID],
       [Date From],
       [Date To],
       [Status],
       [Text]
FROM (
       SELECT   
              MIN( [Rank] ) AS [Rank],
              [Case ID],
              MIN( [Date From] ) AS [Date From],
              MAX( [Date To] ) AS [Date To],
              [Status],
              [Text]
       FROM (
              SELECT
                     RANK() OVER (PARTITION BY [Case ID] ORDER BY [StatusChange] ASC ) AS [Group],
                     *  
              FROM (
                     SELECT 
                           CSL.*,
                           CAST(CSL.[Status] AS Varchar) + '|' + ISNULL( CAST(CSL2.[Status] AS varchar), CAST(CSL.[Status] AS varchar) ) AS [StatusChange]
                     FROM  #TEMPCASE AS CSL
                     LEFT JOIN ( SELECT [Rank], [Status] FROM  #TEMPCASE ) AS CSL2
                           ON CSL2.[Rank] = CSL.[Rank] + 1
              ) AS T1
       ) AS T2
       GROUP BY [Group], [Case ID], [Status], [Text]
) AS T3

标签： sqltsql

解决方案

您可以使用lag()获取“前一个”行（案例）的状态，然后检查它是否与“当前”（案例）行相同。

SELECT [Rank],
       [Case ID],
       [Date From],
       [Date To],
       [Status],
       [Text]
       FROM (SELECT [Rank],
                    [Case ID],
                    [Date From],
                    [Date To],
                    [Status],
                    [Text],
                    lag([Status]) OVER (PARTITION BY [Case ID]
                                        ORDER BY [Date From]) l
                    FROM #tempcase) x
      WHERE l IS NULL
             OR l <> [Status];

db<>小提琴

推荐阅读