'How to loop through a bunch of records with same ID value and select timestamp for one of the records that satisfy the condition?
I have a table, having numerous columns, and ORDER_LINE_ID (abbrev as OL ID), STATUS_ID, STATUS_QTY & INSERT_TS are the fields in concern.
I have a bunch of records with same OL_ID, but their STATUS_ID, QTY & INSERT_TS vary. For that particular value of OL ID,say 123, I want to loop through the bunch of data and check if any of those records have STATUS_ID 3700.8000 (This ID means 'Associate Picked'). If this satisfies, then I must select this record's corresponding STATUS_TS and insert into a field called STORE_RECEIVED_DT.
Assume that the current status is the records at the top. But sometime during the past, it has passed through the STATUS_ID 3700.8000 and then finally has attained 3950.01 status. Now, I want to select the particular INSERT_TS corresponding to 3700.8000 and store in a variable called STORE_RECEIVED_DT.
There are several thousand other OL IDs each repeating a bunch of time with diff status_ids and I want to do this process for all of them. Can someone please suggest a way to overcome this?
This is the current code for reference, but it gives wrong output:
SELECT ORDER_DT,
ORDER_LINE_ID,
ORDER_HEADER_KEY,
ORDER_LINE_KEY,
STATUS_ID AS LINE_STATUS,
--Max(CASE WHEN COLR.STATUS_QTY > 0 THEN COLR.STATUS_QTY END) UNIT,
STATUS_QTY AS UNIT,
STATUS_DESC AS LINE_DESCRIPTION,
ITEM_KEY,
PRODUCT_LINE,
SHIP_NODE_CD,
RECEIVING_NODE_CD,
LINE_TOTAL_AMT, --Nile bpk
ADDITIONAL_LINE_TYPE_CD, --Nile bpk
RETURN_ACTION_CD, --Nile bpk
ORDER_QTY, --Nile bpk
RETURN_REASON_CD, --Nile bpk
RETURN_SUB_REASON_CD, --Nile bpk
RETURN_REASON_DESC, --Nile bpk
RETURN_ACTION, --Nile bpk
STATUS_TS, --Nile bpk
(CASE WHEN (TRIM(STATUS_ID)) ='3700' THEN (STATUS_TS) ELSE NULL END) ORDER_SHIPPED_DT,
(CASE WHEN (TRIM(STATUS_ID)) ='3700.8000' THEN (STATUS_TS) ELSE NULL END) STORE_RECEIVED_DT,
(CASE WHEN (TRIM(STATUS_ID)) ='3700.9000' THEN (STATUS_TS) ELSE NULL END) CUSTOMER_PICKED_UP_DATE,
(CASE WHEN (TRIM(STATUS_ID)) in ('9000','3700.01.545','3200.525','9000.300','3200.520','3700.01.540','1100.525') OR (TRIM(STATUS_ID)) >= '9000' THEN (STATUS_TS) ELSE NULL END) CANCELLED_DATE,
(CASE WHEN (TRIM(STATUS_ID)) ='3700.9000' THEN (STATUS_TS) ELSE NULL END) CUSTOMER_PICKUP_TS, --Nile bpk
(CASE WHEN (TRIM(STATUS_ID)) ='3200.050' THEN (STATUS_TS) ELSE NULL END) ORDER_DROP_TS, --Nile bpk
(CASE WHEN (TRIM(STATUS_ID)) in ('3700.01','3700.01.01','3700.02') THEN (STATUS_TS) ELSE NULL END) RETURN_DATE,
(CASE WHEN (TRIM(STATUS_ID)) in ('1100','1100.200','1100.525','1300','1310') AND STATUS_QTY > 0 THEN (STATUS_QTY) ELSE NULL END) PENDING_OMS_QTY,
(CASE WHEN (TRIM(STATUS_ID)) in ('1500','1500.100','1500.101','3200','3200.050','3200.100','3200.200','3200.500','3200.520') AND STATUS_QTY > 0 THEN (STATUS_QTY) ELSE NULL END) PENDING_SHIPMENT_QTY,
(CASE WHEN (TRIM(STATUS_ID)) in ('3700','3700.00.03','3700.01.03','3700.01.540','3700.500','3700.7777') AND STATUS_QTY > 0 THEN (STATUS_QTY) ELSE NULL END) IN_TRANSIT_QTY,
(CASE WHEN (TRIM(STATUS_ID)) ='3700.8000' AND STATUS_QTY > 0 THEN (STATUS_QTY) ELSE NULL END) AWAITING_PICKUP_QTY,
(CASE WHEN (TRIM(STATUS_ID)) ='3700.9000' AND STATUS_QTY > 0 THEN (STATUS_QTY) ELSE NULL END) CUSTOMER_PICKED_UP_QTY,
(CASE WHEN (TRIM(STATUS_ID)) in ('1300','1310','1500','1500.100') AND STATUS_QTY > 0 THEN (STATUS_QTY) ELSE NULL END) BO_STATUS_QTY
FROM (
SELECT *
, ROW_NUMBER() OVER (
PARTITION BY ORDER_LINE_ID,ORDER_DT ORDER BY STATUS_TS DESC
) AS rn
FROM (
SELECT *
FROM TEMP_SALES_ORDER_DATA
UNION
SELECT *
FROM TEMP_RET_ORDER_DATA
)
) COLR
WHERE COLR.STATUS_ID <= '9000' and COLR.STATUS_ID not in ('3700.01.545','3200.525','9000.300','3200.520','3700.01.540','1100.525') and COLR.STATUS_ID NOT IN ('3700.01.01','3700.02')
Solution 1:[1]
A "generic SQL" solution for this could be to use a "correleated subquery" (query 1) or a joined "derived table" (query 2)
CREATE TABLE Table1
(`ORDER_LINE_ID` int, `STATUS_ID` decimal(8,2), `STATUS_QTY` int, `INSERT_TS` datetime)
;
INSERT INTO Table1
(`ORDER_LINE_ID`, `STATUS_ID`, `STATUS_QTY`, `INSERT_TS`)
VALUES
(712536734, 3950.01, 1, '2021-04-27 23:44:27'),
(712536734, 3700, 0, '2021-04-03 14:44:28'),
(712536734, 3700.8, 5, '2021-04-27 04:44:29'),
(712536734, 3700.01, 0, '2021-04-27 14:10:30')
;
Query 1:
select
order_line_id
, status_id
, status_qty
, insert_ts
, (select insert_ts
from table1 as prev
where prev.order_line_id = t1.order_line_id
and prev.status_id = 3700.8
) as store_received_dt
from table1 as t1
| order_line_id | status_id | status_qty | insert_ts | store_received_dt |
|---------------|-----------|------------|----------------------|----------------------|
| 712536734 | 3950.01 | 1 | 2021-04-27T23:44:27Z | 2021-04-27T04:44:29Z |
| 712536734 | 3700 | 0 | 2021-04-03T14:44:28Z | 2021-04-27T04:44:29Z |
| 712536734 | 3700.8 | 5 | 2021-04-27T04:44:29Z | 2021-04-27T04:44:29Z |
| 712536734 | 3700.01 | 0 | 2021-04-27T14:10:30Z | 2021-04-27T04:44:29Z |
Query 2:
select
t1.order_line_id
, t1.status_id
, t1.status_qty
, t1.insert_ts
, prev.store_received_dt
from table1 as t1
left join (select order_line_id, insert_ts as store_received_dt
from table1 as prev
where status_id = 3700.8
) prev on t1.order_line_id = prev.order_line_id
| order_line_id | status_id | status_qty | insert_ts | store_received_dt |
|---------------|-----------|------------|----------------------|----------------------|
| 712536734 | 3950.01 | 1 | 2021-04-27T23:44:27Z | 2021-04-27T04:44:29Z |
| 712536734 | 3700 | 0 | 2021-04-03T14:44:28Z | 2021-04-27T04:44:29Z |
| 712536734 | 3700.8 | 5 | 2021-04-27T04:44:29Z | 2021-04-27T04:44:29Z |
| 712536734 | 3700.01 | 0 | 2021-04-27T14:10:30Z | 2021-04-27T04:44:29Z |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |