确定列值何时从"include"更改为"exclude"

Question

我有一个数据集，如下所示。

TicketID      TicketLoggedBy        ReportingStatus
55             3356                 Include
56             3357                 Include
57             4001                 Exclude

我试图实现的是创建一个与此表分离的表，该表仅显示从"；包括"；至"；排除"；以及搬家的时间。

这些数据每晚都会公布。到目前为止，我有一个存储过程，它获取TicketID&ReportingStatus并将其与前一晚加载的数据进行比较。如果第一数据集的报告状态＝"0"；包括"；并且最近刷新的报告状态＝"0"；排除"；，然后我设置了一个标志，这样我就可以说这个状态已经更改为排除。我还添加了一个getdate((来说明它何时更改。

我的问题是，我不知道如何才能保持历史性的变化。上述标志将被下一次刷新覆盖(因为状态现在将被排除，因此不会被设置(。

理想情况下，上面的例子应该如下所示：

TicketID      TicketLoggedBy        ReportingStatus       ChangedToExclude       DateChanged
55             3356                 Include                  0                 NULL
56             3357                 Include                  0                 NULL
57             4001                 Exclude                  1               06-Jan-2021

第二天，如果票证57变回包含，但票证55变为排除，则会显示如下所示。

TicketID      TicketLoggedBy        ReportingStatus       ChangedToExclude       DateChanged
55             3356                 Exclude                  1               7-Jan-2021
56             3357                 Include                  0                 NULL
57             4001                 Include                  0                 NULL

而所有的"；ChangedToExclude；标志应保持设置为1，直到状态变回include。

有人对此有什么建议吗？试图解决这个问题让我有点头疼。

Answer 1

下面是使用您提供的数据查找的逻辑示例。它评估每次更新的状态，并将在每次执行时更新您要查找的字段。

/*  Create Temp Tables  */
DROP TABLE IF EXISTS #Production;
CREATE TABLE #Production
(
TicketId INT
, TicketLoggedBy INT
, ReportingStatus CHAR(7)
);
DROP TABLE IF EXISTS #Staging;
CREATE TABLE #Staging
(
TicketId INT
, TicketLoggedBy INT
, ReportingStatus CHAR(7)
);
DROP TABLE IF EXISTS #Logging;
CREATE TABLE #Logging
(
TicketId INT
, TicketLoggedBy INT
, ReportingStatus CHAR(7)
, ChangedToExclude BIT
, DateChanged DATETIME
);
/*  Populate Production  */
INSERT INTO #Production
VALUES
(55, 3356, 'Include')
, (56, 3357, 'Include')
, (57, 4001, 'Include');
/*  Populate Logging with base data  */
INSERT INTO #Logging
SELECT
P.TicketId
, P.TicketLoggedBy
, P.ReportingStatus
, 0
, NULL
FROM
#Production AS P;
/*  Populate Staging with Day 1 values  */
INSERT INTO #Staging
VALUES
(55, 3356, 'Include')
, (56, 3357, 'Include')
, (57, 4001, 'Exclude');
/*  Populate logging with day 1 changes  */
UPDATE
L
SET
L.ReportingStatus = S.ReportingStatus
, L.ChangedToExclude = IIF(L.ReportingStatus = 'Include' AND  S.ReportingStatus = 'Exclude', 1, 0)
, L.DateChanged = IIF(L.ReportingStatus = 'Include' AND S.ReportingStatus = 'Exclude', '01/01/2022', NULL)--Change static date to GETDATE()
FROM
#Logging      AS L
JOIN #Staging AS S
ON L.TicketId = S.TicketId;
/*  Output Day 1 Log  */
SELECT  * FROM  #Logging AS L;
/*  Populate Staging with Day 2 values  */
TRUNCATE TABLE #Staging;
INSERT INTO #Staging
VALUES
(55, 3356, 'Exclude')
, (56, 3357, 'Include')
, (57, 4001, 'Include');
/*  Populate logging with day 2 changes  */
UPDATE
L
SET
L.ReportingStatus = S.ReportingStatus
, L.ChangedToExclude = IIF(L.ReportingStatus = 'Include' AND  S.ReportingStatus = 'Exclude', 1, 0)
, L.DateChanged = IIF(L.ReportingStatus = 'Include' AND S.ReportingStatus = 'Exclude', '01/02/2022', NULL)--Change static date to GETDATE()
FROM
#Logging      AS L
JOIN #Staging AS S
ON L.TicketId = S.TicketId;
/*  Output Day 2 Log  */
SELECT  * FROM  #Logging AS L;
/*  Drop Temp Tables  */
DROP TABLE IF EXISTS #Production;
DROP TABLE IF EXISTS #Staging;
DROP TABLE IF EXISTS #Logging;