I have a table in SQL Server 2014 which stores all the process steps users have done on handheld scanners. The use of the scanners is to pick stock for sales orders.
The table has the following columns:
| ID | JOB_ID | FLOW_STEP_ID | SEQUENCE | SCANNED_DATA | AUDIT_TIME |
The example data is ordered by JOB_ID ASC and AUDIT_TIME ASC
I need to take the SCANNED_DATA column and arrange into a new row, based on the FLOW_STEP_ID.
A JOB_ID covers the instance, i.e. if a user quits the scanning app, a new ID is created when the user reopens the app.
A JOB_ID sees the following pattern for the FLOW_STEP_ID:
Step 302: Order Number
Step 134: Warehouse bin being picked from
Step 97: Stock code
Step 99: Lot Number for stock code
Step 136: Quantity picked
Repeat points 2-5
A JOB_ID can have multiple FLOW_STEP_ID of step 302: Order number. There is a step which should signify the end of a job which is FLOW_STEP_ID of 343, however the user can close the app early, reopen it and therefore have a new JOB_ID and then trigger step 343.
The ID column is unique.
| JOB_ID | SALES_ORDER | BIN | STOCK_CODE | LOT | QTY | AUDIT_TIME |
|--------|-------------|-----|------------|-----|-----|------------|
The new data needs have the JOB_ID and SALES_ORDER for each row until step 302 is repeated or the JOB_ID changes.
I'm completely stuck on this.
Something like this perhaps?
CREATE TABLE #jobs (id int, job_id int,flow_step_id int,sequence int, scanned_data varchar(100),audit_time datetime)
INSERT INTO #jobs
VALUES (8268576,116522,302,1,'somedata', dateadd(minute, -10,getdate()))
, (8268577,116522,134,1,'0', dateadd(minute, -9,getdate()))
, (8268578,116522,97,1,'1', dateadd(minute, -8,getdate()))
, (8268579,116522,99,1,'2', dateadd(minute, -7,getdate()))
, (8268580,116522,136,1,'3', dateadd(minute, -5,getdate()))
, (8268581,116522,302,1,'somedata #2', dateadd(minute, -4,getdate()))
, (8268581,116522,134,1,'1', dateadd(minute, -3,getdate()))
, (8268578,116522,97,1,'2', dateadd(minute, -2,getdate()))
, (8268579,116522,99,1,'3', dateadd(minute, -1,getdate()))
, (8268580,116522,136,1,'4', dateadd(second, -50,getdate()))
, (8268581,116522,302,1,'somedata #2', dateadd(second, -30,getdate()))
, (8268581,116522,134,1,'2', dateadd(second, -29,getdate()))
, (8268578,116522,97,1,'2', dateadd(second, -28,getdate()))
, (8268579,116522,99,1,'3', dateadd(second, -27,getdate()))
, (8268580,116522,136,1,'4', dateadd(second, -26,getdate()))
, (8268581,116522,134,1,'3', dateadd(second, -25,getdate()))
, (8268578,116522,97,1,'3', dateadd(second, -24,getdate()))
, (8268579,116522,99,1,'4', dateadd(second, -23,getdate()))
, (8268580,116522,136,1,'5', dateadd(second, -22,getdate()))
SELECT job_id, batch, batch2
, max(CASE WHEN flow_step_id = 134 THEN scanned_data END) AS bin
, max(CASE WHEN flow_step_id = 97 THEN scanned_data END) AS code
, max(CASE WHEN flow_step_id = 99 THEN scanned_data END) AS lot
, max(CASE WHEN flow_step_id = 136 THEN scanned_data END) AS qty
, min(audit_time) AS start
from (
select *
, SUM(CASE WHEN flow_step_id = 134 THEN 1 END) OVER(partition BY job_id, batch ORDER BY audit_time, id) AS batch2
FROM (
SELECT *, SUM(CASE WHEN flow_step_id = 302 THEN 1 END) OVER(partition BY job_id ORDER BY audit_time, id) AS batch
FROM #jobs
) x
) y
where batch2 is not null
GROUP BY job_id, batch, batch2
DROP TABLE #jobs
This creates a batch id which is just a counter on how many times flow_step 302 was seen. So each new 302 increments it. It's important that they actually start by scanning 302, otherwise it won't work.
Then i do a little conditional aggregation to create the bin, lot, code, qty columns. If you have some stable "sort order" which isn't audit_time, you can use that instead.
Output:
| job_id | batch | batch2 | bin | code | lot | qty | start |
|---|---|---|---|---|---|---|---|
| 116522 | 1 | 1 | 0 | 1 | 2 | 3 | 2023-07-24 10:01:13.047 |
| 116522 | 2 | 1 | 1 | 2 | 3 | 4 | 2023-07-24 10:07:13.047 |
| 116522 | 3 | 1 | 2 | 2 | 3 | 4 | 2023-07-24 10:09:44.047 |
| 116522 | 3 | 2 | 3 | 3 | 4 | 5 | 2023-07-24 10:09:48.047 |