By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @IDs TABLE (
ID INTEGER
,PreviousID INTEGER
,Location INTEGER
)
INSERT INTO @IDs
SELECT 2,null,1235
UNION ALL SELECT 3,2,1236
UNION ALL SELECT 4,3,1239
UNION ALL SELECT 8,null,1237
UNION ALL SELECT 9,8,1234
UNION ALL SELECT 10,9,1235
Select * from @IDs
DECLARE @ORDERID Table (OrderID nvarchar (100))
Insert into @ORDERID values
('4')
,('9')
,('2')
;WITH q AS (
SELECT 0 lvl, ID, PreviousID,PreviousID LastId
,Location,Location as OriginalLocation
FROM @IDs
where ID in (select OrderID from @ORDERID)
UNION ALL
SELECT lvl+1, q.ID,u.PreviousId,q.PreviousId LastId
,q.Location,u.Location
FROM q
INNER JOIN @IDs u ON u.ID = q.PreviousID
)
-- select * from q order by id;
select lvl, ID
| ID | PreviousID | Location |
|---|---|---|
| 2 | null | 1235 |
| 3 | 2 | 1236 |
| 4 | 3 | 1239 |
| 8 | null | 1237 |
| 9 | 8 | 1234 |
| 10 | 9 | 1235 |
| lvl | ID | OriginalId | Location | OriginalLocation | lvl | ID | PreviousID | LastId | Location | OriginalLocation |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2 | 2 | 1235 | 1235 | 0 | 2 | null | null | 1235 | 1235 |
| 0 | 4 | 3 | 1239 | 1239 | 0 | 4 | 3 | 3 | 1239 | 1239 |
| 1 | 4 | 3 | 1239 | 1236 | 1 | 4 | 2 | 3 | 1239 | 1236 |
| 2 | 4 | 2 | 1239 | 1235 | 2 | 4 | null | 2 | 1239 | 1235 |
| 0 | 9 | 8 | 1234 | 1234 | 0 | 9 | 8 | 8 | 1234 | 1234 |
| 1 | 9 | 8 | 1234 | 1237 | 1 | 9 | null | 8 | 1234 | 1237 |
create table ORDER_MED(ORDER_MED_ID int,CHNG_ORDER_MED_ID int,PAT_LOC_ID int
,ORDERING_DATE date);
insert into order_med values
(2,null,1235,'2024-06-02')
,(3,2,1236,'2024-06-03')
,(4,3,1239,'2024-06-04')
,(8,null,1237,'2024-06-09')
,(9,8,1234,'2024-06-09')
,(10,9,1235,'2024-06-10')
;
select * from order_med;
create table CLARITY_DEP(DEPARTMENT_ID int, DEPARTMENT_NAME varchar(50));
insert into clarity_dep values
(1234,'Dep1234')
,(1235,'Dep1235')
,(1236,'Dep1236')
,(1237,'Dep1237')
,(1238,'Dep1238')
,(1239,'Dep1239')
;
select * from clarity_dep;
| ORDER_MED_ID | CHNG_ORDER_MED_ID | PAT_LOC_ID | ORDERING_DATE |
|---|---|---|---|
| 2 | null | 1235 | 2024-06-02 |
| 3 | 2 | 1236 | 2024-06-03 |
| 4 | 3 | 1239 | 2024-06-04 |
| 8 | null | 1237 | 2024-06-09 |
| 9 | 8 | 1234 | 2024-06-09 |
| 10 | 9 | 1235 | 2024-06-10 |
| DEPARTMENT_ID | DEPARTMENT_NAME |
|---|---|
| 1234 | Dep1234 |
| 1235 | Dep1235 |
| 1236 | Dep1236 |
| 1237 | Dep1237 |
| 1238 | Dep1238 |
| 1239 | Dep1239 |
With q as (
Select ORDER_MED_ID CURRENT_ORDERID
,CHNG_ORDER_MED_ID PREV_ORDERID
,CHNG_ORDER_MED_ID as Last_ORDERID
,PAT_LOC_ID as WRITTEN_LOCATION_ID
,PAT_LOC_ID as ORIGINAL_WRITTEN_LOCATION_ID
,0 NumOfTimesRewrite
From ORDER_MED
where ORDER_MED_ID in (4,9,2) -- (Select OrderID from @ORDERID)
UNION ALL
SELECT q.CURRENT_ORDERID
,u.CHNG_ORDER_MED_ID PREV_ORDERID
,q.PREV_ORDERID LAST_ORDERID
,q.WRITTEN_LOCATION_ID
,u.PAT_LOC_ID ORIGINAL_WRITTEN_LOCATION_ID
,NumOfTimesRewrite+1
FROM q inner JOIN ORDER_MED u
ON u.ORDER_MED_ID = q.PREV_ORDERID
)
Select CURRENT_ORDERID
,Coalesce(LAST_ORDERID,CURRENT_ORDERID) as ORIGINAL_ORDERID
,WRITTEN_LOCATION_ID
,ORIGINAL_WRITTEN_LOCATION_ID
,NumOfTimesRewrite
,d1.department_name written_location
,d2.department_name original_written_location
from q
left join clarity_dep d1 on d1.department_id=q.written_location_id
left join clarity_dep d2 on d2.department_id=q.original_written_location_id
where prev_orderid is null
order by q.current_orderid
| CURRENT_ORDERID | ORIGINAL_ORDERID | WRITTEN_LOCATION_ID | ORIGINAL_WRITTEN_LOCATION_ID | NumOfTimesRewrite | written_location | original_written_location |
|---|---|---|---|---|---|---|
| 2 | 2 | 1235 | 1235 | 0 | Dep1235 | Dep1235 |
| 4 | 2 | 1239 | 1235 | 2 | Dep1239 | Dep1235 |
| 9 | 8 | 1234 | 1237 | 1 | Dep1234 | Dep1237 |