SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

(No column name)
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

WITH tabelle(WERKS,DateID,MATNR,Total_Menge_FERT_ABC,WA_Menge_FERT) AS (
SELECT 1010,20230731,59025,99,8.000 UNION ALL SELECT
1010,20230807,59025,82,17.000 UNION ALL SELECT
1010,20230911,59025,16,12.000 UNION ALL SELECT
1010,20240603,59025,0,0.000 UNION ALL SELECT
1010,20240624,59025,0,0.000 UNION ALL SELECT
1044,20230703,59025,61,3.000 UNION ALL SELECT
1044,20230710,59025,58,3.000 UNION ALL SELECT
1044,20230814,59025,48,0.000 UNION ALL SELECT
1044,20230918,59025,43,2.000 UNION ALL SELECT
1044,20231009,59025,38,1.000 UNION ALL SELECT
1044,20231106,59025,33,1.000 UNION ALL SELECT
1044,20240122,59025,31,0.000 UNION ALL SELECT
1044,20240226,59025,31,0.000 UNION ALL SELECT
1044,20240311,59025,31,0.000 UNION ALL SELECT
1044,20240318,59025,31,0.000 UNION ALL SELECT
1044,20240401,59025,31,0.000 UNION ALL SELECT
1044,20240422,59025,31,0.000 UNION ALL SELECT
1044,20240527,59025,31,0.000 UNION ALL SELECT
1044,20240603,59025,31,0.000 UNION ALL SELECT
1044,20240610,59025,31,0.000 UNION ALL SELECT
1010,20230626,94074,1,0.000 UNION ALL SELECT
1010,20230717,94074,1,0.000 UNION ALL SELECT
1010,20230724,94074,1,0.000 UNION ALL SELECT
1010,20230807,94074,1,1.000 UNION ALL SELECT
1010,20230821,94074,0,0.000 UNION ALL SELECT
1010,20231211,94074,0,0.000 UNION ALL SELECT
1010,20240122,94074,0,0.000 UNION ALL SELECT
1010,20240408,94074,0,0.000 UNION ALL SELECT
1010,20240624,94074,0,0.000 UNION ALL SELECT
1010,20230814,59025,62,20.000 UNION ALL SELECT
1010,20231002,59025,0,11.000 UNION ALL SELECT
1010,20240108,59025,0,0.000 UNION ALL SELECT
1010,20240212,59025,0,0.000 UNION ALL SELECT
1010,20240318,59025,0,0.000 UNION ALL SELECT
1010,20240527,59025,0,0.000 UNION ALL SELECT
1010,20240617,59025,0,0.000 UNION ALL SELECT
1044,20230731,59025,48,4.000 UNION ALL SELECT
1044,20230904,59025,46,1.000 UNION ALL SELECT
1044,20231002,59025,39,2.000 UNION ALL SELECT
1044,20231113,59025,32,1.000 UNION ALL SELECT
1044,20231120,59025,32,0.000 UNION ALL SELECT
1044,20231204,59025,32,0.000 UNION ALL SELECT
1044,20240108,59025,31,0.000 UNION ALL SELECT
1044,20240115,59025,31,0.000 UNION ALL SELECT
1044,20240415,59025,31,0.000 UNION ALL SELECT
1044,20240506,59025,31,0.000 UNION ALL SELECT
1010,20231023,94074,0,0.000 UNION ALL SELECT
1010,20231113,94074,0,1.000 UNION ALL SELECT
1010,20231127,94074,0,1.000 UNION ALL SELECT
1010,20231204,94074,0,0.000 UNION ALL SELECT
1010,20240108,94074,0,0.000 UNION ALL SELECT
1010,20240129,94074,0,0.000 UNION ALL SELECT
1010,20240226,94074,0,0.000 UNION ALL SELECT
1010,20240401,94074,0,0.000 UNION ALL SELECT
1010,20240415,94074,0,0.000 UNION ALL SELECT
1010,20230626,59025,23,11.000 UNION ALL SELECT
1010,20230703,59025,133,10.000 UNION ALL SELECT
1010,20230710,59025,124,9.000 UNION ALL SELECT
1010,20230828,59025,44,19.000 UNION ALL SELECT
1010,20230925,59025,0,12.000 UNION ALL SELECT
1010,20231106,59025,0,6.000 UNION ALL SELECT
1010,20231113,59025,0,6.000 UNION ALL SELECT
1010,20231211,59025,0,4.000 UNION ALL SELECT
1010,20231225,59025,0,4.000 UNION ALL SELECT
1010,20240226,59025,0,0.000 UNION ALL SELECT
1010,20240325,59025,0,0.000 UNION ALL SELECT
1010,20240401,59025,0,0.000 UNION ALL SELECT
1010,20240415,59025,0,0.000 UNION ALL SELECT
1010,20240422,59025,0,0.000 UNION ALL SELECT
1044,20230626,59025,39,2.000 UNION ALL SELECT
1044,20230828,59025,47,1.000 UNION ALL SELECT
1044,20231023,59025,35,2.000 UNION ALL SELECT
1044,20231218,59025,32,0.000 UNION ALL SELECT
1044,20240212,59025,31,0.000 UNION ALL SELECT
1044,20240304,59025,31,0.000 UNION ALL SELECT
1010,20230710,94074,1,0.000 UNION ALL SELECT
1010,20230731,94074,1,0.000 UNION ALL SELECT
1010,20230828,94074,0,0.000 UNION ALL SELECT
1010,20231009,94074,1,1.000 UNION ALL SELECT
1010,20231016,94074,1,1.000 UNION ALL SELECT
1010,20240520,94074,0,0.000 UNION ALL SELECT
1010,20240603,94074,0,0.000 UNION ALL SELECT
1010,20230717,59025,115,9.000 UNION ALL SELECT
1010,20230821,59025,63,19.000 UNION ALL SELECT
1010,20230918,59025,3,13.000 UNION ALL SELECT
1010,20231016,59025,0,4.000 UNION ALL SELECT
1010,20231023,59025,0,5.000 UNION ALL SELECT
1010,20231030,59025,0,5.000 UNION ALL SELECT
1010,20231218,59025,0,3.000 UNION ALL SELECT
1010,20240115,59025,0,0.000 UNION ALL SELECT
1010,20240122,59025,0,0.000 UNION ALL SELECT
1010,20240408,59025,0,0.000 UNION ALL SELECT
1044,20230807,59025,48,0.000 UNION ALL SELECT
1044,20230821,59025,48,0.000 UNION ALL SELECT
1044,20230911,59025,45,1.000 UNION ALL SELECT
1044,20230925,59025,41,2.000 UNION ALL SELECT
1044,20231016,59025,37,1.000 UNION ALL SELECT
1044,20231030,59025,34,1.000 UNION ALL SELECT
1044,20231211,59025,32,0.000 UNION ALL SELECT
1044,20240101,59025,31,1.000 UNION ALL SELECT
1044,20240129,59025,31,0.000 UNION ALL SELECT
1010,20230703,94074,1,0.000 UNION ALL SELECT
1010,20230911,94074,0,0.000 UNION ALL SELECT
1010,20230918,94074,3,0.000 UNION ALL SELECT
1010,20230925,94074,0,0.000 UNION ALL SELECT
1010,20231002,94074,0,0.000 UNION ALL SELECT
1010,20231218,94074,0,0.000 UNION ALL SELECT
1010,20231225,94074,0,0.000 UNION ALL SELECT
1010,20240219,94074,0,0.000 UNION ALL SELECT
1010,20240304,94074,0,0.000 UNION ALL SELECT
1010,20240325,94074,0,0.000 UNION ALL SELECT
1010,20240429,94074,0,0.000 UNION ALL SELECT
1010,20231009,59025,0,4.000 UNION ALL SELECT
1010,20231120,59025,0,7.000 UNION ALL SELECT
1010,20231127,59025,0,6.000 UNION ALL SELECT
1010,20240129,59025,0,0.000 UNION ALL SELECT
1010,20240205,59025,0,0.000 UNION ALL SELECT
1010,20240219,59025,0,0.000 UNION ALL SELECT
1010,20240304,59025,0,0.000 UNION ALL SELECT
1010,20240429,59025,0,0.000 UNION ALL SELECT
1010,20240513,59025,0,0.000 UNION ALL SELECT
1044,20230717,59025,55,3.000 UNION ALL SELECT
1044,20231225,59025,32,0.000 UNION ALL SELECT
1044,20240205,59025,31,0.000 UNION ALL SELECT
1044,20240408,59025,31,0.000 UNION ALL SELECT
1010,20231030,94074,0,1.000 UNION ALL SELECT
1010,20231106,94074,0,0.000 UNION ALL SELECT
1010,20240101,94074,0,0.000 UNION ALL SELECT
1010,20240115,94074,0,0.000 UNION ALL SELECT
1010,20240311,94074,0,0.000 UNION ALL SELECT
1010,20240318,94074,0,0.000 UNION ALL SELECT
1010,20240506,94074,0,0.000 UNION ALL SELECT
1010,20240513,94074,0,0.000 UNION ALL SELECT
1010,20240527,94074,0,0.000 UNION ALL SELECT
1010,20240610,94074,0,0.000 UNION ALL SELECT
1010,20240617,94074,0,0.000 UNION ALL SELECT
1010,20230724,59025,107,8.000 UNION ALL SELECT
1010,20230904,59025,28,16.000 UNION ALL SELECT
1010,20231204,59025,0,5.000 UNION ALL SELECT
1010,20240101,59025,0,3.000 UNION ALL SELECT
1010,20240311,59025,0,0.000 UNION ALL SELECT
1010,20240506,59025,0,0.000 UNION ALL SELECT
1010,20240520,59025,0,0.000 UNION ALL SELECT
1010,20240610,59025,0,0.000 UNION ALL SELECT
1044,20230724,59025,52,3.000 UNION ALL SELECT
1044,20231127,59025,32,0.000 UNION ALL SELECT
1044,20240219,59025,31,0.000 UNION ALL SELECT
1044,20240325,59025,31,0.000 UNION ALL SELECT
1044,20240429,59025,31,0.000 UNION ALL SELECT
1044,20240513,59025,31,0.000 UNION ALL SELECT
1044,20240520,59025,31,0.000 UNION ALL SELECT
1044,20240617,59025,31,0.000 UNION ALL SELECT
1044,20240624,59025,31,0.000 UNION ALL SELECT
1010,20230814,94074,0,0.000 UNION ALL SELECT
1010,20230904,94074,0,0.000 UNION ALL SELECT
1010,20231120,94074,0,0.000 UNION ALL SELECT
1010,20240205,94074,0,0.000 UNION ALL SELECT
1010,20240212,94074,0,0.000 UNION ALL SELECT
1010,20240422,94074,0,0.000
), t0 AS (
--t0 dient einzig und allein dazu aus DateID ein brauchbares Datum zu machen
SELECT	WERKS,
		DateID,
		dateadd(day,convert(INT,right(convert(CHAR(8),DateID),2))-1,
		dateadd(month,convert(INT,right(left(convert(CHAR(8),DateID),6),2))-1,
		dateadd(year,convert(INT,left(convert(CHAR(8),DateID),4))-1900,0)
		)) AS [Date],
		MATNR,
		cast(Total_Menge_FERT_ABC AS INT) AS Total_Menge_FERT_ABC,
		cast(WA_Menge_FERT AS INT) AS WA_Menge_FERT
FROM	tabelle
), t1 AS (
--Basis vom CTE
SELECT	DateID,
		[Date],
		Total_Menge_FERT_ABC,
		WERKS,
		MATNR,
		WA_Menge_FERT,
		Total_Menge_FERT_ABC - WA_Menge_FERT AS rest,
		1 AS [count]
FROM	t0
UNION ALL
--Rekursion vom CTE
SELECT	t1.DateID,
		t1.[Date],
		t1.Total_Menge_FERT_ABC,
		t1.WERKS,
		t1.MATNR,
		t1.WA_Menge_FERT,
		t1.rest - t0.WA_Menge_FERT AS rest,
		t1.[count] + 1 AS [count]
FROM	t1
INNER JOIN t0
ON		t1.WERKS = t0.WERKS
AND		t1.MATNR = t0.MATNR
AND		dateadd(day,t1.[count] * 7,t1.[Date]) = t0.[Date]
--der max(count) ist später interessant, also beim letzten Datensatz count = gesuchter Wert
WHERE	t1.rest > 0
--bei count = 99 dauert es dann 99 Wochen oder länger, hier wird eine Endlosrekursion verhindert und die Performance kann verbessert werden in dem Härtefälle nicht weiter durchgerechnet werden
AND		t1.[count] + 1 <= 99
)
SELECT	DateID,
		[Date],
		Total_Menge_FERT_ABC,
		WERKS,
		MATNR,
		WA_Menge_FERT,
--		rest,
		[count] AS gesuchter_Wert
FROM	t1
WHERE	rest <= 0
ORDER BY WERKS, MATNR, [Date], [count]

DateID	Date	Total_Menge_FERT_ABC	WERKS	MATNR	WA_Menge_FERT	gesuchter_Wert
20230626	2023-06-26 00:00:00.000	23	1010	59025	11	3
20230703	2023-07-03 00:00:00.000	133	1010	59025	10	10
20230710	2023-07-10 00:00:00.000	124	1010	59025	9	9
20230717	2023-07-17 00:00:00.000	115	1010	59025	9	8
20230724	2023-07-24 00:00:00.000	107	1010	59025	8	7
20230731	2023-07-31 00:00:00.000	99	1010	59025	8	6
20230807	2023-08-07 00:00:00.000	82	1010	59025	17	5
20230814	2023-08-14 00:00:00.000	62	1010	59025	20	4
20230821	2023-08-21 00:00:00.000	63	1010	59025	19	4
20230828	2023-08-28 00:00:00.000	44	1010	59025	19	3
20230904	2023-09-04 00:00:00.000	28	1010	59025	16	2
20230911	2023-09-11 00:00:00.000	16	1010	59025	12	2
20230918	2023-09-18 00:00:00.000	3	1010	59025	13	1
20230925	2023-09-25 00:00:00.000	0	1010	59025	12	1
20231002	2023-10-02 00:00:00.000	0	1010	59025	11	1
20231009	2023-10-09 00:00:00.000	0	1010	59025	4	1
20231016	2023-10-16 00:00:00.000	0	1010	59025	4	1
20231023	2023-10-23 00:00:00.000	0	1010	59025	5	1
20231030	2023-10-30 00:00:00.000	0	1010	59025	5	1
20231106	2023-11-06 00:00:00.000	0	1010	59025	6	1
20231113	2023-11-13 00:00:00.000	0	1010	59025	6	1
20231120	2023-11-20 00:00:00.000	0	1010	59025	7	1
20231127	2023-11-27 00:00:00.000	0	1010	59025	6	1
20231204	2023-12-04 00:00:00.000	0	1010	59025	5	1
20231211	2023-12-11 00:00:00.000	0	1010	59025	4	1
20231218	2023-12-18 00:00:00.000	0	1010	59025	3	1
20231225	2023-12-25 00:00:00.000	0	1010	59025	4	1
20240101	2024-01-01 00:00:00.000	0	1010	59025	3	1
20240108	2024-01-08 00:00:00.000	0	1010	59025	0	1
20240115	2024-01-15 00:00:00.000	0	1010	59025	0	1
20240122	2024-01-22 00:00:00.000	0	1010	59025	0	1
20240129	2024-01-29 00:00:00.000	0	1010	59025	0	1
20240205	2024-02-05 00:00:00.000	0	1010	59025	0	1
20240212	2024-02-12 00:00:00.000	0	1010	59025	0	1
20240219	2024-02-19 00:00:00.000	0	1010	59025	0	1
20240226	2024-02-26 00:00:00.000	0	1010	59025	0	1
20240304	2024-03-04 00:00:00.000	0	1010	59025	0	1
20240311	2024-03-11 00:00:00.000	0	1010	59025	0	1
20240318	2024-03-18 00:00:00.000	0	1010	59025	0	1
20240325	2024-03-25 00:00:00.000	0	1010	59025	0	1
20240401	2024-04-01 00:00:00.000	0	1010	59025	0	1
20240408	2024-04-08 00:00:00.000	0	1010	59025	0	1
20240415	2024-04-15 00:00:00.000	0	1010	59025	0	1
20240422	2024-04-22 00:00:00.000	0	1010	59025	0	1
20240429	2024-04-29 00:00:00.000	0	1010	59025	0	1
20240506	2024-05-06 00:00:00.000	0	1010	59025	0	1
20240513	2024-05-13 00:00:00.000	0	1010	59025	0	1
20240520	2024-05-20 00:00:00.000	0	1010	59025	0	1
20240527	2024-05-27 00:00:00.000	0	1010	59025	0	1
20240603	2024-06-03 00:00:00.000	0	1010	59025	0	1
20240610	2024-06-10 00:00:00.000	0	1010	59025	0	1
20240617	2024-06-17 00:00:00.000	0	1010	59025	0	1
20240624	2024-06-24 00:00:00.000	0	1010	59025	0	1
20230626	2023-06-26 00:00:00.000	1	1010	94074	0	7
20230703	2023-07-03 00:00:00.000	1	1010	94074	0	6
20230710	2023-07-10 00:00:00.000	1	1010	94074	0	5
20230717	2023-07-17 00:00:00.000	1	1010	94074	0	4
20230724	2023-07-24 00:00:00.000	1	1010	94074	0	3
20230731	2023-07-31 00:00:00.000	1	1010	94074	0	2
20230807	2023-08-07 00:00:00.000	1	1010	94074	1	1
20230814	2023-08-14 00:00:00.000	0	1010	94074	0	1
20230821	2023-08-21 00:00:00.000	0	1010	94074	0	1
20230828	2023-08-28 00:00:00.000	0	1010	94074	0	1
20230904	2023-09-04 00:00:00.000	0	1010	94074	0	1
20230911	2023-09-11 00:00:00.000	0	1010	94074	0	1
20230918	2023-09-18 00:00:00.000	3	1010	94074	0	7
20230925	2023-09-25 00:00:00.000	0	1010	94074	0	1
20231002	2023-10-02 00:00:00.000	0	1010	94074	0	1
20231009	2023-10-09 00:00:00.000	1	1010	94074	1	1
20231016	2023-10-16 00:00:00.000	1	1010	94074	1	1
20231023	2023-10-23 00:00:00.000	0	1010	94074	0	1
20231030	2023-10-30 00:00:00.000	0	1010	94074	1	1
20231106	2023-11-06 00:00:00.000	0	1010	94074	0	1
20231113	2023-11-13 00:00:00.000	0	1010	94074	1	1
20231120	2023-11-20 00:00:00.000	0	1010	94074	0	1
20231127	2023-11-27 00:00:00.000	0	1010	94074	1	1
20231204	2023-12-04 00:00:00.000	0	1010	94074	0	1
20231211	2023-12-11 00:00:00.000	0	1010	94074	0	1
20231218	2023-12-18 00:00:00.000	0	1010	94074	0	1
20231225	2023-12-25 00:00:00.000	0	1010	94074	0	1
20240101	2024-01-01 00:00:00.000	0	1010	94074	0	1
20240108	2024-01-08 00:00:00.000	0	1010	94074	0	1
20240115	2024-01-15 00:00:00.000	0	1010	94074	0	1
20240122	2024-01-22 00:00:00.000	0	1010	94074	0	1
20240129	2024-01-29 00:00:00.000	0	1010	94074	0	1
20240205	2024-02-05 00:00:00.000	0	1010	94074	0	1
20240212	2024-02-12 00:00:00.000	0	1010	94074	0	1
20240219	2024-02-19 00:00:00.000	0	1010	94074	0	1
20240226	2024-02-26 00:00:00.000	0	1010	94074	0	1
20240304	2024-03-04 00:00:00.000	0	1010	94074	0	1
20240311	2024-03-11 00:00:00.000	0	1010	94074	0	1
20240318	2024-03-18 00:00:00.000	0	1010	94074	0	1
20240325	2024-03-25 00:00:00.000	0	1010	94074	0	1
20240401	2024-04-01 00:00:00.000	0	1010	94074	0	1
20240408	2024-04-08 00:00:00.000	0	1010	94074	0	1
20240415	2024-04-15 00:00:00.000	0	1010	94074	0	1
20240422	2024-04-22 00:00:00.000	0	1010	94074	0	1
20240429	2024-04-29 00:00:00.000	0	1010	94074	0	1
20240506	2024-05-06 00:00:00.000	0	1010	94074	0	1
20240513	2024-05-13 00:00:00.000	0	1010	94074	0	1
20240520	2024-05-20 00:00:00.000	0	1010	94074	0	1
20240527	2024-05-27 00:00:00.000	0	1010	94074	0	1
20240603	2024-06-03 00:00:00.000	0	1010	94074	0	1
20240610	2024-06-10 00:00:00.000	0	1010	94074	0	1
20240617	2024-06-17 00:00:00.000	0	1010	94074	0	1
20240624	2024-06-24 00:00:00.000	0	1010	94074	0	1