基于多个条件将多个状态检索到一行中SQL 2008

Question

这是表格的样子：

If Runs today (Monday Sept 28)
(101, 'Submitted','07/31/2020 13:35:41'),
(101, 'Processing','07/31/2020 15:19:23'),
(101, 'Tracking', '9/26/2020 9:18:48'),
(101, 'Approved', '9/26/2020 10:16:48'),
(101, 'Received', '9/27/2020 8:16:48'),
(101, 'Closed', '9/27/2020 9:16:48'),

Output: Processing > Tracking > Approved > Received > Closed

IF Runs Friday (Sept 25)
(102, 'Complete', '8/10/2020 9:16:41'),
(102, 'Declined', '8/13/2020 9:18:48'),
(102, 'Reviewing','9/24/2020 17:59:13'),
(102, 'Testing', '9/24/2020 19:16:05'),
(102, 'Approved', '9/24/2020 21:42:56'),

Output: Declined > Reviewing >  Testing > Approved

IF Runs Wedns (Sept 23)
(104, 'Approved', '6/20/2020 12:19:17'),
(104, 'Sent', '7/20/2020 12:16:17'),
(104, 'Testing', '9/22/2020 17:46:16'),

Output: Sent > Testing

IF Runs Tues (Sept 22)
(105, 'Sent', '9/21/2020 5:46:51'),
(105, NULL, '9/21/2019 9:53:53'),

Output: This row should not come up in the report since the previous status is not listed in the
category list.

IF Runs Yesterday (Sunday Sept 27)
(107, 'Approved', '9/10/2019 9:53:54'),
(107, 'Reviewing', '9/20/2019 9:53:54'),
(107, 'Started', '9/23/2019 9:53:54'),
(107, 'Approved', '9/25/2020 9:40:54'),
(107, 'Reviewing', '9/25/2020 10:50:54'),
(107, 'Received', '9/25/2020 10:53:54'),    
(107, 'Tracking', '9/26/2020 9:58:54'),
(107, 'Received', '9/26/2020 10:53:54')

Output: Started > Approved > Reviewing > Received > Tracking >Received

状态类别列表：（跟踪、批准、审核、接收、测试、关闭）

条件：

WITH CTE AS (SELECT *, Row_number() over (PARTITION BY ID ORDER BY statusDate desc) RN FROM YourTable),
 CTE2 as (SELECT * FROM cte WHERE StatusDate >= (CASE WHEN DATENAME(DW,StatusDate) = 'Monday' THEN  dateadd(DD, -1, cast(getdate() as date) + dateadd(DD, -2, cast(getdate() as date) + dateadd(DD, -3, cast(getdate() as date) WHEN DATENAME(DW,StatusDate) = 'Sunday' THEN  dateadd(DD, -1, cast(getdate() as date) + dateadd(DD, -2, cast(getdate() as date) ELSE ateadd(DD, -1, cast(getdate() as date)) and _Status in ('Tracking', 'Approved', 'Reviewing', 'Received', 'Testing', 'Closed')),  CTE3 as (SELECT * FROM CTE2 UNION SELECT * FROM CTE where RN  = 2 and ID in (SELECT ID FROM CTE2)) SELECT ID, left(_Status, len(_Status)-2) as _Status FROM (SELECT Distinct t1.ID,
  STUFF((SELECT t2._Status + ' > '
  FROM CTE3 t2
  WHERE t1.ID = t2.ID
  ORDER BY StatusDate
  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,0,'') _Status from CTE2 t1) Z

Answer 1

这似乎适用于提供的示例数据。我需要更多的样本数据来测试所有可能的边缘情况。

小提琴：http://www.sqlfiddle.com/#！18/33 d76/9/0（其他人可以随意偷小提琴来测试！）

• 首先，我为每个记录分配一个行号，这样我就可以根据您的限制，将那些只有1行深的ID重新添加到行中...(CTE)注意，我们按每个ID内降序的日期排序，所以最新的记录首先列出。这样，如果在同一天发生变化，第2行仍然是集合中的记录，并在联合中被消除；但是，如果一天只存在1条记录，则先前的newestrecord将变为第2行，并随后通过CT3将其包含在数据集中。
• CTE2根据规则将数据限制为您希望查看的数据
• CTE3在只有1行的记录中联合，因此您可以获得优先状态。这是工作的，因为我不关心重复的原因，因为联合消除了它们。
• 最后，我们使用CTE2的数据集填充cte3中的数据，以生成组合状态
• 使用包装器查询消除使用包装器查询结尾的额外'>'。

WITH CTE AS (SELECT *, Row_number() over (PARTITION BY ID ORDER BY statusDate desc) RN FROM YourTable),
     CTE2 as (SELECT * FROM cte WHERE StatusDate >= dateadd(DD, -1, cast(getdate() as date))
                                  and _Status in ('Tracking', 'Approved', 'Reviewing', 'Received', 'Testing', 'Closed')),
     CTE3 as (SELECT * FROM CTE2 UNION SELECT * FROM CTE where RN  = 2 and ID in (SELECT ID FROM CTE2))

SELECT ID, left(_Status, len(_Status)-2) as _Status
FROM (SELECT Distinct t1.ID,
      STUFF((SELECT t2._Status + ' > '
      FROM CTE3 t2
      WHERE t1.ID = t2.ID
      ORDER BY StatusDate
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,0,'') _Status
from CTE2 t1) Z

ID  _Status
101 Processing > Tracking
102 Approved > Reviewing > Approved
107 Tracking > Received