I’m challenged with summarising the total estimated machine time vs the total actual machine time for a list of jobs based on a date field. The issue is that all estimate and actual entries are in one column.
I need help transposing this data into separate columns for easier analysis.
This is a simplified example of what the table looks like…
| JobNo | OperationDesc | OperationLength | Quantity | PostingType | StartDateAndTime |
|---|---|---|---|---|---|
| 345271 | Durst 1330: MR | 0.33 | 1 | 1 | 2021-04-27 16:00 |
| 345271 | Durst 1330: Run | 1.5 | 15 | 1 | 2021-04-27 16:00 |
| 345271 | Durst3 IDC | 0.5 | 3 | 2 | 2021-04-28 10:00 |
| 345271 | Durst3 IDC | 0.75 | 5 | 2 | 2021-04-28 11:00 |
| 345271 | Durst3 IDC | 1 | 10 | 2 | 2021-04-28 12:00 |
PostingType “1” is the estimate and “2” is actual.
What I’m trying to get is a table looking like this….
| JobNo | EstimatedMachine | EstimatedTime | QuantityOrdered | MachineUsed | TotalTime | TotalQuantity |
|---|---|---|---|---|---|---|
| 345271 | Durst 1330 | 1.83 | 15 | Durst3 | 2.25 | 18 |
The code I’ve worked out so far is this..
SELECT JobNo
,(SELECT TOP 1 LEFT(OperationDesc, CHARINDEX(':', OperationDesc)-1) FROM TSE WHERE PostingType = '1') [MachineEstimated]
,(SELECT SUM(OperationLength) FROM TSE WHERE PostingType = '1') [EstimatedTime]
,(SELECT SUM(Quantity) FROM TSE WHERE PostingType = '1' AND OperationDesc NOT LIKE '%MR%') [QuantityOrdered]
,(SELECT TOP 1 LEFT(OperationDesc, CHARINDEX(' ', OperationDesc)-1) FROM TSE WHERE PostingType = '2') [MachineUsed]
,(SELECT SUM(OperationLength) FROM TSE WHERE PostingType = '2') [TotalTime]
,(SELECT SUM(Quantity) FROM TSE WHERE PostingType = '2') [TotalQuantity]
FROM TSE
WHERE StartDateAndTime >= '2021-04-28T06:00:00.000'
AND StartDateAndTime <= '2021-04-29T59:59:00.000'
GROUP BY JobNo
The results I get do only include the job numbers that ran (PostingType 2) in the day selected (28th) but the other columns sum the total of the entire table and only pull through the first OperationDesc of the table rather than only the 24 hour day specified.
What I’m I doing wrong? Is there a better way to get what I need?
Many thanks,
Advertisement
Answer
I think you just want conditional aggregation:
SELECT JobNo,
MAX(CASE WHEN PostingType = 1 THEN LEFT(OperationDesc, CHARINDEX(':', OperationDesc) - 1) END) as MachineEstimated,
SUM(CASE WHEN PostingType = 1 THEN OperationLength END) as EstimatedTime,
SUM(CASE WHEN PostingType = 1 Quantity END) as QuantityOrdered,
MAX(CASE WHEN PostingType = 2 THEN LEFT(OperationDesc, CHARINDEX(':', OperationDesc) - 1) END) as MachineUsed,
SUM(CASE WHEN PostingType = 2 THEN OperationLength END) as TotalTime,
SUM(CASE WHEN PostingType = 2 Quantity END) as TotalQuantity
FROM TSE
WHERE StartDateAndTime >= '2021-04-28T06:00:00.000' AND
StartDateAndTime <= '2021-04-29T59:59:00.000'
GROUP BY JobNo;
Note: PostingType looks like a number, so I dropped the single quotes. If it is really a string, then use the single quotes. Don’t mix data types.