By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) Jul 25 2020 11:26:55 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
create table #t_sample_dataset (
Top_lvl_article VARCHAR(255),
Prev_article VARCHAR(255),
Current_article VARCHAR(255),
Qty float,
Lvl int,
"Order" VARCHAR(255)
);
insert into #t_sample_dataset values
('80542.296', '80542.296', '80542.296', 1 ,0, '80542.296'),
('80542.296', '80542.296', '80537.127', 1 ,1, '80542.296 ||80537.127'),
('80542.296', '80542.296', '80537.969', 1 ,1, '80542.296 ||80537.969'),
('80542.296', '80542.296', '80540.285', 1 ,1, '80542.296 ||80540.285'),
('80542.296', '80540.285', '01583.1527-H', 1 ,2, '80542.296 ||80540.285||01583.1527-H'),
('80542.296', '80540.285', '80539.398', 1 ,2, '80542.296 ||80540.285||80539.398'),
('80542.296', '80540.285', '80540.05', 1 ,2, '80542.296 ||80540.285||80540.050'),
('80542.296', '80540.05', '80540.050U', 1 ,3, '80542.296 ||80540.285||80540.050||80540.050U'),
('80542.296', '80540.050U', '80540.092', 0.001 ,4, '80542.296 ||80540.285||80540.050||80540.050U||80540.092'),
('80542.296', '80540.092', '80532.395', 0.023 ,5, '80542.296 ||80540.285||80540.050||80540.050U||80540.092||80532.395');
10 rows affected
WITH CTE_Top_levels
(Lvl,
"Order",
Top_lvl_article,
Prev_Article,
Current_Article,
Qty,
total_qty
)
AS (
select ts.lvl,
ts."order",
ts.top_lvl_article,
ts.prev_article,
ts.Current_Article,
ts.qty,
ts.qty
from #t_sample_dataset ts
where ts.lvl = 0
UNION ALL
select ts.lvl,
ts."order",
ts.top_lvl_article,
ts.prev_article,
ts.Current_Article,
ts.qty,
cte.total_qty + ts.qty
from CTE_Top_levels cte
join #t_sample_dataset ts
on ts.Prev_Article = cte.Current_Article
and cte.Top_lvl_article = ts.top_lvl_article
and ts.lvl <> 0
) select Top_lvl_article, Prev_Article, Current_Article, Qty, Lvl, Total_qty, "Order" from cte_top_levels
| Top_lvl_article | Prev_Article | Current_Article | Qty | Lvl | Total_qty | Order |
|---|---|---|---|---|---|---|
| 80542.296 | 80542.296 | 80542.296 | 1 | 0 | 1 | 80542.296 |
| 80542.296 | 80542.296 | 80537.127 | 1 | 1 | 2 | 80542.296 ||80537.127 |
| 80542.296 | 80542.296 | 80537.969 | 1 | 1 | 2 | 80542.296 ||80537.969 |
| 80542.296 | 80542.296 | 80540.285 | 1 | 1 | 2 | 80542.296 ||80540.285 |
| 80542.296 | 80540.285 | 01583.1527-H | 1 | 2 | 3 | 80542.296 ||80540.285||01583.1527-H |
| 80542.296 | 80540.285 | 80539.398 | 1 | 2 | 3 | 80542.296 ||80540.285||80539.398 |
| 80542.296 | 80540.285 | 80540.05 | 1 | 2 | 3 | 80542.296 ||80540.285||80540.050 |
| 80542.296 | 80540.05 | 80540.050U | 1 | 3 | 4 | 80542.296 ||80540.285||80540.050||80540.050U |
| 80542.296 | 80540.050U | 80540.092 | 0.001 | 4 | 4.001 | 80542.296 ||80540.285||80540.050||80540.050U||80540.092 |
| 80542.296 | 80540.092 | 80532.395 | 0.023 | 5 | 4.024 | 80542.296 ||80540.285||80540.050||80540.050U||80540.092||80532.395 |