I want to be able to trace backwards through a table of movement of materials, equipment used to move it and timings, starting at a specified value in a field and finding that value in another field in different row(s) based on a shared value.
In the table below, I need to be able to trace back, for example, from the EndCode 'M' all the related rows all the way back to StartCode A. I might then want to be able to trace back through all rows from EndCode 'U' back to StartCode 'N'.
In the table, StartCode (material) A and B are moved to become EndCode C. EndCode C then become StartCodeD, is then moved with StartCode E to become EndCode F etc. The orange/blue rows represent groups of material movements.
From this, I want to create a new table/view which brings through the StartedAt time of the next event as a new field named 'EndedAt'. It will look like this:
I've created a recursive query that uses ROW_NUMBER and CTE to bring through the StartedAt as a new field as "EndedAt".
I then tried to use a nested SELECT statement to find all the StartCodes related to EndCode 'M' in an attempt to loopback through the table to search for all the relevant EndCodes in the StartCode column. This only brings through a few of the rows. I treid I repeat the nested statement a few times it brings through different rows.
I need help to pick up all the relevant Start/End Codes needed to create the desired table/view.
Eventually a report will be attached to the table allowing the table to be filterable by a chosen EndCode parameter (e.g. M, U etc.)
CREATE TABLE MyTable (
`StartCode` VARCHAR(1),
`StartedAt` DATETIME,
`EndCode` VARCHAR(1)
);
INSERT INTO MyTable (`StartCode`, `StartedAt`, `EndCode`)
VALUES
('A', '01/01/2019 01:00', 'C'),
('B', '01/01/2019 02:15', 'C'),
('C', '01/01/2019 03:00', 'F'),
('D', '01/01/2019 03:19', 'F'),
('E', '01/01/2019 04:00', 'F'),
('F', '01/01/2019 04:14', 'G'),
('G', '01/01/2019 05:00', 'J'),
('H', '01/01/2019 05:37', 'J'),
('I', '01/01/2019 05:45', 'J'),
('J', '01/01/2019 06:00', 'L'),
('K', '01/01/2019 06:09', 'L'),
('L', '01/01/2019 07:00', 'M'),
('N', '01/01/2019 09:20', 'P'),
('O', '01/01/2019 09:37', 'P'),
('P', '01/01/2019 09:45', 'Q'),
('Q', '01/01/2019 11:00', 'T'),
('R', '01/01/2019 11:10', 'T'),
('S', '01/01/2019 11:47', 'T'),
('T', '01/01/2019 11:58', 'U');
The EndedAt is a simple join:
SELECT
S.StartCode,
S.StartedAt,
S.EndCode,
E.StartedAt AS EndedAt
FROM
MyTable AS S
LEFT JOIN MyTable AS E ON S.EndCode = E.StartCode
Result:
StartCode StartedAt EndCode EndedAt
A 2019-01-01 01:00:00.000 C 2019-01-01 03:00:00.000
B 2019-01-01 02:15:00.000 C 2019-01-01 03:00:00.000
C 2019-01-01 03:00:00.000 F 2019-01-01 04:14:00.000
D 2019-01-01 03:19:00.000 F 2019-01-01 04:14:00.000
E 2019-01-01 04:00:00.000 F 2019-01-01 04:14:00.000
F 2019-01-01 04:14:00.000 G 2019-01-01 05:00:00.000
G 2019-01-01 05:00:00.000 J 2019-01-01 06:00:00.000
H 2019-01-01 05:37:00.000 J 2019-01-01 06:00:00.000
I 2019-01-01 05:45:00.000 J 2019-01-01 06:00:00.000
J 2019-01-01 06:00:00.000 L 2019-01-01 07:00:00.000
K 2019-01-01 06:09:00.000 L 2019-01-01 07:00:00.000
L 2019-01-01 07:00:00.000 M NULL
N 2019-01-01 09:20:00.000 P 2019-01-01 09:45:00.000
O 2019-01-01 09:37:00.000 P 2019-01-01 09:45:00.000
P 2019-01-01 09:45:00.000 Q 2019-01-01 11:00:00.000
Q 2019-01-01 11:00:00.000 T 2019-01-01 11:58:00.000
R 2019-01-01 11:10:00.000 T 2019-01-01 11:58:00.000
S 2019-01-01 11:47:00.000 T 2019-01-01 11:58:00.000
T 2019-01-01 11:58:00.000 U NULL
You can display the hierarchies with the following (recursive CTE with bottoms up approach in this case). Make sure that you have no loops in your data first.
Edit: If you want to check for any step in the hierarchy and upwards, the anchor needs to be any code (not just the last M or U), so I removed the WHERE in the anchor.
DECLARE @EndCodeFilter CHAR(1) = 'J'
;WITH RecursiveCodes AS
(
-- Anchor
SELECT
LastCode = M.EndCode,
CurrentCode = M.StartCode,
PreviousCode = M.EndCode,
RecursionLevel = 1,
RecursionPath = CONVERT(NVARCHAR(MAX), M.EndCode + '->' + M.StartCode),
CurrentStartAt = M.StartedAt
FROM
MyTable AS M
UNION ALL
-- Recursion: link related codes
SELECT
LastCode = R.LastCode,
CurrentCode = M.StartCode,
PreviousCode = M.EndCode,
RecursionLevel = R.RecursionLevel + 1,
RecursionPath = R.RecursionPath + '->' + M.StartCode,
CurrentStartAt = M.StartedAt
FROM
RecursiveCodes AS R
INNER JOIN MyTable AS M ON R.CurrentCode = M.EndCode
)
SELECT
R.CurrentCode,
R.CurrentStartAt,
R.LastCode,
EndedAt = E.StartedAt,
R.PreviousCode,
R.RecursionLevel,
R.RecursionPath
FROM
RecursiveCodes AS R
LEFT JOIN MyTable AS E ON R.LastCode = E.StartCode
WHERE
R.LastCode = @EndCodeFilter
ORDER BY
R.CurrentCode,
R.LastCode
OPTION
(MAXRECURSION 0)
Result:
CurrentCode CurrentStartAt LastCode EndedAt PreviousCode RecursionLevel RecursionPath
A 2019-01-01 01:00:00.000 J 2019-01-01 06:00:00.000 C 4 J->G->F->C->A
B 2019-01-01 02:15:00.000 J 2019-01-01 06:00:00.000 C 4 J->G->F->C->B
C 2019-01-01 03:00:00.000 J 2019-01-01 06:00:00.000 F 3 J->G->F->C
D 2019-01-01 03:19:00.000 J 2019-01-01 06:00:00.000 F 3 J->G->F->D
E 2019-01-01 04:00:00.000 J 2019-01-01 06:00:00.000 F 3 J->G->F->E
F 2019-01-01 04:14:00.000 J 2019-01-01 06:00:00.000 G 2 J->G->F
G 2019-01-01 05:00:00.000 J 2019-01-01 06:00:00.000 J 1 J->G
H 2019-01-01 05:37:00.000 J 2019-01-01 06:00:00.000 J 1 J->H
I 2019-01-01 05:45:00.000 J 2019-01-01 06:00:00.000 J 1 J->I