# A table that calculates the inventory of each of the products for each day

The company sells two products: CNC machine (id_sku = 1) and hammer drill (id_sku = 2).
The median daily sales for the CNC machine are 2 units, and for the hammer drill, it's 7 units.
Current inventory: CNC machines - 5 units, hammer drills - 0 units.
There is a delivery schedule:

id_sku supply_id date_supply quantity
1 2 01.09.2023 10
1 4 03.09.2023 2
1 6 25.09.2023 3
1 8 25.10.2023 8
1 10 10.11.2023 11
2 12 07.09.2023 4
2 14 10.09.2023 33
2 16 11.10.2023 55
2 18 11.11.2023 12

Please help me to create a table that calculates the inventory of each of the products for each day from the current date until November 30, 2023.

hi

its not clear to me

You should provide consumable test date with dates in ISO format:

``````SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #t
(
id_sku int NOT NULL
,supply_id int NOT NULL
,date_supply date NOT NULL
,quantity int NOT NULL
,PRIMARY KEY (id_sku, supply_id, date_supply)
);
GO
INSERT INTO #t
VALUES (1, 2, '20230901', 10)
,(1, 4, '20230903', 2)
,(1, 6, '20230925', 3)
,(1, 8, '20231025', 8)
,(1, 10, '20231110', 11)
,(2, 12, '20230907', 4)
,(2, 14, '20230910', 33)
,(2, 16, '20231011', 55)
,(2, 18, '20231111', 12);
GO``````

You should also provide text showing what the results of the test data should look like. I am going to assume you want to estimate stock based on deliveries and median sales.

A calendar table is good for this. I am just going to use the following DateRange function.

``````WITH SKUs(id_sku, PerDay, Initial)
AS
(
SELECT 1, 2, 5
UNION ALL
SELECT 2, 7, 0
)
,TotalQuanity
AS
(
SELECT id_sku, supply_id, date_supply
,SUM(quantity) OVER (PARTITION BY id_sku ORDER BY date_supply) AS quantity
FROM #t
)
SELECT S.id_sku, CAST(R.[Value] AS date) AS date_supply
/* Quantity - Sold */
,(S.Initial + ISNULL(SUM(T.quantity) OVER (PARTITION BY S.id_sku ORDER BY R.[Value]), 0))
- S.PerDay * ROW_NUMBER() OVER (PARTITION BY S.id_sku ORDER BY R.[Value]) AS OnHand
FROM dbo.DateRange('20230828', '20231130', 'dd', 1) R
CROSS JOIN SKUs S
LEFT JOIN TotalQuanity T
ON R.[value] = T.date_supply
ORDER BY id_sku, date_supply;``````