sql-server - SQL Server - 使用 PIVOT 聚合
问题描述
我在使用 PIVOT 功能时遇到了一些困难。我有来自十四个不同的子装配站的生产数据。我想按主组件对它们进行分组，并在表格中显示每个子组件的状态。这是细分：
原始数据样本：
+-----------------------+------------------+-----------------+-------------+-----------+------------+-------------------------+-------------------------+-----------+
| ID | OrderID | ProductionOrder | Workstation | Status | Date | StartTime | EndTime | TotalTime |
+-----------------------+------------------+-----------------+-------------+-----------+------------+-------------------------+-------------------------+-----------+
| 60127429_1000_1_S120 | 60127429_1000_1 | 108100 | S120 | Completed | 05/12/2020 | 2020-05-12 12:44:00.000 | 2020-05-12 13:02:00.000 | 18 |
| 60127429_1000_1_S090 | 60127429_1000_1 | 108100 | S090 | Completed | 05/12/2020 | 2020-05-12 12:20:00.000 | 2020-05-12 12:44:00.000 | 24 |
| 60127429_1000_1_S080 | 60127429_1000_1 | 108100 | S080 | Completed | 05/12/2020 | 2020-05-12 10:40:00.000 | 2020-05-12 10:47:00.000 | 7 |
| 60127429_1000_1_S035 | 60127429_1000_1 | 108100 | S035 | Completed | 05/13/2020 | 2020-05-13 10:39:00.000 | 2020-05-13 12:40:00.000 | 121 |
| 60127527_2000_1_S120 | 60127527_2000_1 | 883331 | S120 | Completed | 05/12/2020 | 2020-05-12 12:09:00.000 | 2020-05-12 12:28:00.000 | 19 |
| 60127527_2000_1_S090 | 60127527_2000_1 | 883331 | S090 | Completed | 05/12/2020 | 2020-05-12 11:47:00.000 | 2020-05-12 12:06:00.000 | 19 |
| 60127527_2000_1_S080 | 60127527_2000_1 | 883331 | S080 | Completed | 05/12/2020 | 2020-05-12 10:31:00.000 | 2020-05-12 10:38:00.000 | 7 |
| 60127527_2000_1_S070 | 60127527_2000_1 | 883331 | S070 | Completed | 05/12/2020 | 2020-05-12 09:24:04.400 | 2020-05-12 09:24:00.000 | 0 |
| 60127527_5025_1_S020 | 60127527_5000_1 | 880051 | S020 | Completed | 05/12/2020 | 2020-05-12 07:14:04.550 | 2020-05-12 07:34:00.000 | 20 |
| 60127527_5000_1_S120 | 60127527_5000_1 | 880049 | S120 | Completed | 05/12/2020 | 2020-05-12 09:57:00.000 | 2020-05-12 10:17:00.000 | 20 |
| 60127527_5000_1_S090 | 60127527_5000_1 | 880049 | S090 | Completed | 05/12/2020 | 2020-05-12 09:38:00.000 | 2020-05-12 09:53:00.000 | 15 |
| 60127527_5000_1_S070 | 60127527_5000_1 | 880049 | S070 | Completed | 05/12/2020 | 2020-05-12 09:23:00.000 | 2020-05-12 10:25:00.000 | 62 |
| 60128137_11025_1_S020 | 60128137_11000_1 | 108338 | S020 | Completed | 05/14/2020 | 2020-05-14 10:04:09.877 | 2020-05-14 10:28:00.000 | 24 |
| 60128137_11025_1_S010 | 60128137_11000_1 | 108338 | S010 | Completed | 05/14/2020 | 2020-05-14 07:55:00.000 | 2020-05-14 08:48:00.000 | 53 |
| 60128137_11000_1_S120 | 60128137_11000_1 | 108333 | S120 | Completed | 05/18/2020 | 2020-05-18 08:24:00.000 | 2020-05-18 09:54:00.000 | 90 |
| 60128137_11000_1_S110 | 60128137_11000_1 | 108333 | S110 | Planned | 05/19/2020 | NULL | NULL | NULL |
| 60128137_11000_1_S090 | 60128137_11000_1 | 108333 | S090 | Completed | 05/18/2020 | 2020-05-18 07:52:00.000 | 2020-05-18 08:11:00.000 | 19 |
| 60128137_11000_1_S080 | 60128137_11000_1 | 108333 | S080 | Completed | 05/18/2020 | 2020-05-18 07:21:00.000 | 2020-05-18 07:27:00.000 | 6 |
| 60128137_11000_1_S070 | 60128137_11000_1 | 108333 | S070 | Planned | 05/19/2020 | NULL | NULL | NULL |
| 60128137_11000_1_S060 | 60128137_11000_1 | 108333 | S060 | Completed | 05/14/2020 | 2020-05-14 07:33:00.000 | 2020-05-14 08:03:00.000 | 30 |
| 60128137_11000_1_S050 | 60128137_11000_1 | 108333 | S050 | Planned | 05/19/2020 | NULL | NULL | NULL |
| 60128137_11000_1_S040 | 60128137_11000_1 | 108333 | S040 | Completed | 05/13/2020 | 2020-05-13 10:34:00.000 | 2020-05-13 12:15:00.000 | 101 |
| 60128137_11000_1_S035 | 60128137_11000_1 | 108333 | S035 | Completed | 05/13/2020 | 2020-05-13 07:58:00.000 | 2020-05-13 08:29:00.000 | 31 |
| 60128137_11000_1_S030 | 60128137_11000_1 | 108333 | S030 | Completed | 05/13/2020 | 2020-05-13 09:46:00.000 | 2020-05-13 09:51:00.000 | 5 |
| 60128137_2025_1_S020 | 60128137_2000_1 | 108334 | S020 | Completed | 05/13/2020 | 2020-05-13 14:42:04.890 | 2020-05-13 15:06:00.000 | 24 |
| 60128137_2025_1_S010 | 60128137_2000_1 | 108334 | S010 | Planned | 05/19/2020 | NULL | NULL | NULL |
| 60128137_2000_1_S120 | 60128137_2000_1 | 108329 | S120 | Completed | 05/14/2020 | 2020-05-14 13:13:00.000 | 2020-05-14 14:24:00.000 | 71 |
| 60128137_2000_1_S110 | 60128137_2000_1 | 108329 | S110 | Planned | 05/19/2020 | NULL | NULL | NULL |
| 60128137_2000_1_S090 | 60128137_2000_1 | 108329 | S090 | Completed | 05/14/2020 | 2020-05-14 12:37:00.000 | 2020-05-14 13:04:00.000 | 27 |
| 60128137_2000_1_S080 | 60128137_2000_1 | 108329 | S080 | Completed | 05/14/2020 | 2020-05-14 12:03:00.000 | 2020-05-14 12:27:00.000 | 24 |
| 60128137_2000_1_S070 | 60128137_2000_1 | 108329 | S070 | Completed | 05/14/2020 | 2020-05-14 07:56:00.000 | 2020-05-14 12:09:00.000 | 253 |
| 60128137_2000_1_S060 | 60128137_2000_1 | 108329 | S060 | Completed | 05/13/2020 | 2020-05-13 12:38:00.000 | 2020-05-13 12:57:00.000 | 19 |
| 60128137_2000_1_S050 | 60128137_2000_1 | 108329 | S050 | Completed | 05/14/2020 | 2020-05-14 07:28:39.143 | 2020-05-14 07:30:00.000 | 2 |
| 60128137_2000_1_S040 | 60128137_2000_1 | 108329 | S040 | Completed | 05/13/2020 | 2020-05-13 08:26:00.000 | 2020-05-13 08:58:00.000 | 32 |
| 60128137_2000_1_S035 | 60128137_2000_1 | 108329 | S035 | Completed | 05/12/2020 | 2020-05-12 14:41:00.000 | 2020-05-12 15:02:00.000 | 21 |
| 60128137_2000_1_S030 | 60128137_2000_1 | 108329 | S030 | Completed | 05/13/2020 | 2020-05-13 07:47:00.000 | 2020-05-13 08:21:00.000 | 34 |
| 60128137_4000_1_S080 | 60128137_4000_1 | 108330 | S080 | Completed | 05/18/2020 | 2020-05-18 09:19:00.000 | 2020-05-18 09:29:00.000 | 10 |
| 60128137_4000_1_S070 | 60128137_4000_1 | 108330 | S070 | Completed | 05/18/2020 | 2020-05-18 07:10:00.000 | 2020-05-18 08:51:00.000 | 101 |
| 60128137_4000_1_S060 | 60128137_4000_1 | 108330 | S060 | Completed | 05/13/2020 | 2020-05-13 12:59:00.000 | 2020-05-13 13:17:00.000 | 18 |
| 60128137_4000_1_S050 | 60128137_4000_1 | 108330 | S050 | Planned | 05/19/2020 | NULL | NULL | NULL |
| 60128137_4000_1_S040 | 60128137_4000_1 | 108330 | S040 | Completed | 05/13/2020 | 2020-05-13 09:57:00.000 | 2020-05-13 10:01:00.000 | 4 |
| 60128137_4000_1_S035 | 60128137_4000_1 | 108330 | S035 | Completed | 05/12/2020 | 2020-05-12 14:25:00.000 | 2020-05-12 15:14:00.000 | 49 |
+-----------------------+------------------+-----------------+-------------+-----------+------------+-------------------------+-------------------------+-----------+
我使用此代码来透视数据：
SELECT *
FROM
(
SELECT master.[Customer],
master.[OrderID],
master.[SalesOrderNo],
master.[LineNo],
master.[OrderQty],
master.[Workstation],
master.[Status],
master.ShipDate
FROM
[dbo].[MES Master Data] as master
WHERE [ParentID] IN
(
SELECT TOP (20) [ParentID]
FROM [MES Master Data]
WHERE EndTime IS NOT NULL AND DATEDIFF(DAY, [EndTime], GETDATE()) < 2
Group By [ParentID]
ORDER BY MAX([Workstation]) DESC, MAX([EndTime]) DESC
)
) AS SourceTable PIVOT(MAX([Status]) FOR [Workstation] IN([S010],
[S020],
[S025],
[S030],
[S035],
[S040],
[S050],
[S060],
[S070],
[S080],
[S090],
[S100],
[S110],
[S120])) AS PivotTable
这工作相对没问题。我认为我过滤表格的逻辑可能会更好。我试图仅获取过去两天已处理的订单，并且只查看在startedorcompleted状态中至少有一个站点的订单。
这是我查看视图时数据的样子：
+------------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------+---------+-----------+
| OrderID | S010 | S020 | S025 | S030 | S035 | S040 | S050 | S060 | S070 | S080 | S090 | S100 | S110 | S120 |
+------------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------+---------+-----------+
| 60128569_5000_1 | Planned | Planned | Started | Completed | Completed | Completed | Planned | Completed | Planned | Inactive | Inactive | Inactive | Planned | Inactive |
| 60129128_2000_1 | Completed | Completed | NULL | Completed | Completed | Completed | Completed | Completed | Planned | Completed | Completed | NULL | Planned | Completed |
| 60129128_2000_2 | Completed | Completed | NULL | Completed | Completed | Completed | Planned | Completed | Completed | Completed | Completed | NULL | Planned | Completed |
| 60129438_2000_1 | Planned | Started | Completed | Completed | Completed | Completed | Completed | Completed | Planned | Completed | Completed | Inactive | Planned | Started |
| 60129438_2000_2 | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Planned | Started | Inactive | Planned | Inactive |
| 60129428_12000_1 | Planned | Completed | NULL | Completed | Completed | Completed | Completed | Completed | Planned | Completed | Completed | NULL | Planned | Completed |
| 60129428_13000_1 | Completed | Completed | Planned | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Inactive | Planned | Completed |
| 60129428_5000_1 | Planned | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Inactive | Planned | Completed |
| 60129428_8000_1 | Planned | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Started | Completed | Completed | Inactive | Planned | Started |
| 60128369_1000_1 | Planned | Completed | Completed | Planned | Planned | Completed | Completed | Completed | Planned | Completed | Completed | Planned | Planned | Completed |
| 60128369_1000_2 | Completed | Completed | Completed | Planned | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Inactive | Planned | Completed |
| 60128369_1000_3 | Inactive | Completed | Completed | Planned | Completed | Planned | Completed | Completed | Completed | Completed | Completed | Inactive | Planned | Completed |
| 60128369_2000_1 | Planned | Completed | Completed | Completed | Completed | Completed | Planned | Completed | Completed | Completed | Completed | Inactive | Planned | Completed |
| 60128137_11000_1 | Completed | Completed | NULL | Completed | Completed | Completed | Planned | Completed | Planned | Completed | Completed | NULL | Planned | Completed |
| 60128137_4000_1 | Planned | Completed | NULL | Completed | Completed | Completed | Planned | Completed | Completed | Completed | Completed | NULL | Planned | Completed |
| 60129588_1000_1 | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Inactive | Planned | Completed |
| 60128174_1000_1 | Planned | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Inactive | Planned | Completed |
| 60128174_2000_1 | Planned | Started | Completed | Planned | Completed | Completed | Completed | Completed | Completed | Completed | Completed | Inactive | Planned | Completed |
| 60129024_28000_1 | Planned | Suspended | Completed | Completed | Completed | Completed | Started | Completed | Planned | Inactive | Inactive | Inactive | Planned | Inactive |
| 60129614_4000_1 | Planned | Completed | NULL | Completed | Completed | Completed | Started | Completed | Started | Inactive | Inactive | NULL | Planned | Inactive |
| 60129614_4000_2 | Completed | Completed | NULL | Completed | Completed | Completed | Started | Completed | Planned | Inactive | Inactive | NULL | Planned | Inactive |
| 60129614_4000_3 | Completed | Suspended | NULL | Completed | Completed | Completed | Started | Completed | Planned | Inactive | Inactive | NULL | Planned | Inactive |
| 60129614_4000_4 | Inactive | Suspended | NULL | Completed | Completed | Suspended | Inactive | Completed | Planned | Inactive | Inactive | NULL | Planned | Inactive |
| 60128601_1000_1 | Planned | Planned | Planned | Completed | Completed | Completed | Planned | Completed | Planned | Inactive | Inactive | Inactive | Planned | Inactive |
| 60128603_2000_1 | Planned | Planned | NULL | Completed | Planned | Completed | Planned | Completed | Planned | Inactive | Inactive | NULL | Planned | Inactive |
| 60128603_3000_1 | Planned | Planned | Planned | Completed | Planned | Completed | Planned | Planned | Inactive | Inactive | Inactive | Inactive | Planned | Inactive |
| 60128603_7000_1 | Inactive | Inactive | Inactive | Completed | Planned | Started | Inactive | Inactive | Inactive | Inactive | Inactive | Inactive | Planned | Inactive |
+------------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------+---------+-----------+
以下是我想改进的地方：
Master Data通过仅显示具有至少一个状态为started或的站点的整体订单来过滤原始订单completed。通过仅显示EndTime最近两天内的订单来进一步过滤。- 添加另一个名为“LastActiveStation”的列，它将显示订单处理的最新站点。（生产流程是线性的，从 S010 -> S120。）
- 添加另一个名为“LastActiveTime”的列，它将
EndTime在“LastActiveStation”中显示该站的最新信息
我真的在最后两颗子弹上挣扎。无论我做什么，我似乎都无法正确聚合。
提前感谢您阅读这篇冗长的帖子，我将不胜感激。
解决方案
诀窍是在您旋转后将额外的字段合并回来。我喜欢用通用表表达式将它们分解以使其更具可读性，请参见下面的示例。请注意，第一个（巨大的）cte 只是以可用的形式获取您的示例数据，真正的工作从 cteDateLimit 开始。您不想一遍又一遍地调用 GETDATE 和 DATEADD，定义一次限制并在后续 CTE 中使用它。
;with cteData as (
SELECT * FROM (VALUES ('60127429_1000_1_S120', '60127429_1000_1', '108100', 'S120', 'Completed', '05/12/2020', '2020-05-12 12:44:00.000', '2020-05-12 13:02:00.000', '18')
,('60127429_1000_1_S090', '60127429_1000_1', '108100', 'S090', 'Completed', '05/12/2020', '2020-05-12 12:20:00.000', '2020-05-12 12:44:00.000', '24')
,('60127429_1000_1_S080', '60127429_1000_1', '108100', 'S080', 'Completed', '05/12/2020', '2020-05-12 10:40:00.000', '2020-05-12 10:47:00.000', '7')
,('60127429_1000_1_S035', '60127429_1000_1', '108100', 'S035', 'Completed', '05/13/2020', '2020-05-13 10:39:00.000', '2020-05-13 12:40:00.000', '121')
,('60127527_2000_1_S120', '60127527_2000_1', '883331', 'S120', 'Completed', '05/12/2020', '2020-05-12 12:09:00.000', '2020-05-12 12:28:00.000', '19')
,('60127527_2000_1_S090', '60127527_2000_1', '883331', 'S090', 'Completed', '05/12/2020', '2020-05-12 11:47:00.000', '2020-05-12 12:06:00.000', '19')
,('60127527_2000_1_S080', '60127527_2000_1', '883331', 'S080', 'Completed', '05/12/2020', '2020-05-12 10:31:00.000', '2020-05-12 10:38:00.000', '7')
,('60127527_2000_1_S070', '60127527_2000_1', '883331', 'S070', 'Completed', '05/12/2020', '2020-05-12 09:24:04.400', '2020-05-12 09:24:00.000', '0')
,('60127527_5025_1_S020', '60127527_5000_1', '880051', 'S020', 'Completed', '05/12/2020', '2020-05-12 07:14:04.550', '2020-05-12 07:34:00.000', '20')
,('60127527_5000_1_S120', '60127527_5000_1', '880049', 'S120', 'Completed', '05/12/2020', '2020-05-12 09:57:00.000', '2020-05-12 10:17:00.000', '20')
,('60127527_5000_1_S090', '60127527_5000_1', '880049', 'S090', 'Completed', '05/12/2020', '2020-05-12 09:38:00.000', '2020-05-12 09:53:00.000', '15')
,('60127527_5000_1_S070', '60127527_5000_1', '880049', 'S070', 'Completed', '05/12/2020', '2020-05-12 09:23:00.000', '2020-05-12 10:25:00.000', '62')
,('60128137_11025_1_S020', '60128137_11000_1', '108338', 'S020', 'Completed', '05/14/2020', '2020-05-14 10:04:09.877', '2020-05-14 10:28:00.000', '24')
,('60128137_11025_1_S010', '60128137_11000_1', '108338', 'S010', 'Completed', '05/14/2020', '2020-05-14 07:55:00.000', '2020-05-14 08:48:00.000', '53')
,('60128137_11000_1_S120', '60128137_11000_1', '108333', 'S120', 'Completed', '05/18/2020', '2020-05-18 08:24:00.000', '2020-05-18 09:54:00.000', '90')
,('60128137_11000_1_S110', '60128137_11000_1', '108333', 'S110', 'Planned', '05/19/2020', NULL, NULL, NULL)
,('60128137_11000_1_S090', '60128137_11000_1', '108333', 'S090', 'Completed', '05/18/2020', '2020-05-18 07:52:00.000', '2020-05-18 08:11:00.000', '19')
,('60128137_11000_1_S080', '60128137_11000_1', '108333', 'S080', 'Completed', '05/18/2020', '2020-05-18 07:21:00.000', '2020-05-18 07:27:00.000', '6')
,('60128137_11000_1_S070', '60128137_11000_1', '108333', 'S070', 'Planned', '05/19/2020', NULL, NULL, NULL)
,('60128137_11000_1_S060', '60128137_11000_1', '108333', 'S060', 'Completed', '05/14/2020', '2020-05-14 07:33:00.000', '2020-05-14 08:03:00.000', '30')
,('60128137_11000_1_S050', '60128137_11000_1', '108333', 'S050', 'Planned', '05/19/2020', NULL, NULL, NULL)
,('60128137_11000_1_S040', '60128137_11000_1', '108333', 'S040', 'Completed', '05/13/2020', '2020-05-13 10:34:00.000', '2020-05-13 12:15:00.000', '101')
,('60128137_11000_1_S035', '60128137_11000_1', '108333', 'S035', 'Completed', '05/13/2020', '2020-05-13 07:58:00.000', '2020-05-13 08:29:00.000', '31')
,('60128137_11000_1_S030', '60128137_11000_1', '108333', 'S030', 'Completed', '05/13/2020', '2020-05-13 09:46:00.000', '2020-05-13 09:51:00.000', '5')
,('60128137_2025_1_S020', '60128137_2000_1', '108334', 'S020', 'Completed', '05/13/2020', '2020-05-13 14:42:04.890', '2020-05-13 15:06:00.000', '24')
,('60128137_2025_1_S010', '60128137_2000_1', '108334', 'S010', 'Planned', '05/19/2020', NULL, NULL, NULL)
,('60128137_2000_1_S120', '60128137_2000_1', '108329', 'S120', 'Completed', '05/14/2020', '2020-05-14 13:13:00.000', '2020-05-14 14:24:00.000', '71')
,('60128137_2000_1_S110', '60128137_2000_1', '108329', 'S110', 'Planned', '05/19/2020', NULL, NULL, NULL)
,('60128137_2000_1_S090', '60128137_2000_1', '108329', 'S090', 'Completed', '05/14/2020', '2020-05-14 12:37:00.000', '2020-05-14 13:04:00.000', '27')
,('60128137_2000_1_S080', '60128137_2000_1', '108329', 'S080', 'Completed', '05/14/2020', '2020-05-14 12:03:00.000', '2020-05-14 12:27:00.000', '24')
,('60128137_2000_1_S070', '60128137_2000_1', '108329', 'S070', 'Completed', '05/14/2020', '2020-05-14 07:56:00.000', '2020-05-14 12:09:00.000', '253')
,('60128137_2000_1_S060', '60128137_2000_1', '108329', 'S060', 'Completed', '05/13/2020', '2020-05-13 12:38:00.000', '2020-05-13 12:57:00.000', '19')
,('60128137_2000_1_S050', '60128137_2000_1', '108329', 'S050', 'Completed', '05/14/2020', '2020-05-14 07:28:39.143', '2020-05-14 07:30:00.000', '2')
,('60128137_2000_1_S040', '60128137_2000_1', '108329', 'S040', 'Completed', '05/13/2020', '2020-05-13 08:26:00.000', '2020-05-13 08:58:00.000', '32')
,('60128137_2000_1_S035', '60128137_2000_1', '108329', 'S035', 'Completed', '05/12/2020', '2020-05-12 14:41:00.000', '2020-05-12 15:02:00.000', '21')
,('60128137_2000_1_S030', '60128137_2000_1', '108329', 'S030', 'Completed', '05/13/2020', '2020-05-13 07:47:00.000', '2020-05-13 08:21:00.000', '34')
,('60128137_4000_1_S080', '60128137_4000_1', '108330', 'S080', 'Completed', '05/18/2020', '2020-05-18 09:19:00.000', '2020-05-18 09:29:00.000', '10')
,('60128137_4000_1_S070', '60128137_4000_1', '108330', 'S070', 'Completed', '05/18/2020', '2020-05-18 07:10:00.000', '2020-05-18 08:51:00.000', '101')
,('60128137_4000_1_S060', '60128137_4000_1', '108330', 'S060', 'Completed', '05/13/2020', '2020-05-13 12:59:00.000', '2020-05-13 13:17:00.000', '18')
,('60128137_4000_1_S050', '60128137_4000_1', '108330', 'S050', 'Planned', '05/19/2020', NULL, NULL, NULL)
,('60128137_4000_1_S040', '60128137_4000_1', '108330', 'S040', 'Completed', '05/13/2020', '2020-05-13 09:57:00.000', '2020-05-13 10:01:00.000', '4')
,('60128137_4000_1_S035', '60128137_4000_1', '108330', 'S035', 'Completed', '05/12/2020', '2020-05-12 14:25:00.000', '2020-05-12 15:14:00.000', '49')
) as DataIn(ID, OrderID, ProductionOrder, Workstation, WorkStatus, ProcDate, StartTime, EndTime, TotalTime)
),cteDateLimit as ( SELECT DATEADD(DAY, -6, GETDATE()) as DayLimit
), cteFiltOrder as (
SELECT DISTINCT OrderID FROM cteData as D CROSS JOIN cteDateLimit as L
WHERE D.EndTime >= L.DayLimit
AND EXISTS (SELECT * FROM cteData as D2 WHERE D.OrderID = D2.OrderID
AND D2.WorkStatus IN ('Completed', 'Started'))
--NOTE: Your spec as written passes a part with a new EndTime and an older WorkStatus,
--If you want to require the COMPLETED or STARTED to be in the time limit also
-- then replace the whole EXISTS clause with D.WorkStatus IN ('Completed', 'Started')
), cteLastOp as (--Figure out the last operation you did on the part
SELECT D.ID as LastID , D.OrderID as LastOrdID
, ROW_NUMBER () OVER (PARTITION BY D.OrderID ORDER BY ENDTIME DESC) as RowNewness
FROM cteData as D INNER JOIN cteFiltOrder as F on F.OrderID = D.OrderID
), cteExtra as (--Capture details of the last operation to merge back in after pivot
SELECT D.OrderID, D.Workstation as LastActiveStation
, D.EndTime as LastActiveTime
FROM cteData as D INNER JOIN cteLastOp as L on L.LastID = D.ID
WHERE L.RowNewness = 1
), cteFiltered as ( --Get all operations on the Order
SELECT D.*
FROM cteData as D INNER JOIN cteLastOp as L ON L.LastOrdID = D.OrderID
WHERE L.RowNewness = 1
), ctePivot as ( --generate teh pivot, it can only handle 1 non-pivot field
SELECT * FROM (SELECT OrderID, Workstation, WorkStatus FROM cteFiltered) as SourceTable
PIVOT (MAX(WorkStatus) FOR Workstation IN ([S010],[S020]
,[S025],[S030],[S035],[S040],[S050],[S060],[S070]
,[S080],[S090],[S100],[S110],[S120])) AS PivotTable
)SELECT E.LastActiveStation , E.LastActiveTime , P.*
FROM ctePivot as P INNER JOIN cteExtra as E on P.OrderID = E.OrderID
基本流程是首先定义您的日期限制，然后使用它来获取 OrderID 的列表，这些 OrderID 至少有一个处于 COMPLETED 或 STARTED 状态的操作，并且某些步骤在过去 2 天内结束。请注意，我在这里使用了 6 天，因为我太少了，只需将 -6 更改为 -2 或其他什么。
下一步是查找每个 OrderID 的最后一个操作，我使用按 EndTime 降序排序的 ROW_NUMBER 来标识最后一个，如果您只想排除“计划”操作，则可以在此处添加排除项。
您还可以插入更多 CTE，这些 CTE 会生成有关 OrderID 的额外信息，例如花费的总时间。当你在 cteExtra 中合并时，只需添加一个新的 CTE 并将其合并到最后。
接下来，我将符合日期选择标准的 OrderID 列表加入整个数据集，因此我只使用将出现在输出中的行，我称之为 cteFiltered
Next PIVOT 并将 OrderID 和旋转工作站存储在 ctePivot
最后一步是将我们在 cteExtra 中捕获的有关 orderID 的额外信息合并回 Pivot 以获得完整输出。
推荐阅读
- excel - 多次调用函数
- android - 如何使用 Kotlin 从用户那里获取名称并将其显示在 Toast 中？
- python - AttributeError: 'Series' object has no attribute 'as_matrix' 为什么会出错？
- java - Android - 收到推送通知 FCM 后，如何在我的 Activity 中更新数据或 UI？
- c# - 空合并赋值运算符的行为
- kubernetes - Kubernetes。是否可以将卷安装到包含 pod id 的路径？
- file - DSpace 文件的位置
- r - 更改数据框列表中的一列并使用应用函数保留 changde 数据框而不是更改输出
- c# - 在 WebBrowser 控件中获取 HtmlElement 的当前/计算样式
- python - Firebase 云消息传递 HTTP v1 发送错误 - 400 客户端错误：对 url 的错误请求：https://fcm.googleapis.com/v1/projects/
/消息：发送