I am having some difficulties with the PIVOT function. I have production data coming out of fourteen different subassembly stations. I would like to group them by main assembly and display the status of each subassembly in a table. Here's the breakdown:
Sample of Raw Data:
+-----------------------+------------------+-----------------+-------------+-----------+------------+-------------------------+-------------------------+-----------+
| 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 |
+-----------------------+------------------+-----------------+-------------+-----------+------------+-------------------------+-------------------------+-----------+
I used this code to pivot the data:
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
This works relatively okay. I think my logic in filtering the table could be better. I am trying to get only the orders that have been worked on in the last two days and only look at the ones that have atleast one station in the started or completed status.
This is what that data looks like when I look at the view:
+------------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------+---------+-----------+
| 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 |
+------------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+----------+---------+-----------+
Here is what I would like to improve on:
Master Data by only showing overall orders that have atleast one station that has a status of started or completed. Filter this further by only showing those orders that have an EndTime that is within the last two days.EndTime for the station in "LastActiveStation" I'm really struggling with the last two bullets. No matter what I do, I can't seem to get the aggregations right.
The trick is to merge the extra fields back in after you pivot. I like to break them up with Common Table Expressions to make it more readable, see the example below. Note the first (huge) cte is just getting your example data in useable form, the real work starts with cteDateLimit. You don't want to call GETDATE and DATEADD over and over, define the limit once and use it in subsequent CTEs.
;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
The basic flow is first define your date limit, then use it to get a list of OrderIDs that have at least one operation in COMPLETED or STARTED status and have some step ended in the last 2 days. Note that I used 6 days here because I was getting too few, just change the -6 to -2 or whatever.
The next step is to find the last operation for each OrderID, I use ROW_NUMBER ordered on EndTime descending to identify the last, you could add an exclusion there if you only wanted to, for example, exclude "Planned" operations from consideration.
You could also insert more CTEs that generate extra information about an OrderID, like total time spent. Just add a new CTE and merge it in at the end when you merge in cteExtra.
Next I JOIN the list of OrderIDs that meed the date selection criteria to the whole dataset so I'm working with only rows that will appear in the output, I call it cteFiltered
Next PIVOT and store the OrderID and the pivoted workstations in ctePivot
Last step is to merge the extra information about orderIDs that we captures in cteExtra back into the Pivot to get the full output.