I have below data and the output i need is in Column: New Inventory. Currently using Cursor to derive the values but there is performance issue as the no.of record increases in source table.
| ID | Item | Inventory | Projected Shipment | Receipt | LinkDate | NewInventory |
|---|---|---|---|---|---|---|
| 1 | Item1 | '10' | '2' | '5' | '01-Sep-2021' | '13' |
| 1 | Item2 | '20' | '3' | '8' | '02-Sep-2021' | '18' |
| 1 | Item3 | '30' | '8' | '15' | '03-Sep-2021' | '25' |
| 2 | Item4 | '40' | '10' | '20' | '01-Sep-2021' | '50' |
| 2 | Item5 | '50' | '25' | '20' | '02-Sep-2021' | '45' |
| 2 | Item6 | '60' | '10' | '20' | '03-Sep-2021' | '55' |
This is how the value is calculated: for each partition based on ID Order by LinkDate the first row will be based on this formula:
NewInventory = Inventory Receipt-ProjectedShipment
So the First record becomes : 10 5-3 =13. This value becomes the base inventory and will be used in subsequent rows for calculating the New Invenotry in the same partition.
For Instance 'Item2' for ID=1
New Inventory = Previous calculated NewInventory Current Row Receipt- Current Row Projected Shipment
This Becomes
New Inventory = 13 8- 3-- This becomes 18
Currently all this logic is implemented with help of Cursor. Please let me know if there is a direct method with tsql only so as to address current performance issue.
Please help.
Thanks in advance
CodePudding user response：
You can do it with FIRST_VALUE() and SUM() window functions:
SELECT *,
FIRST_VALUE(Inventory) OVER (PARTITION BY ID ORDER BY LinkDate)
SUM(Receipt - ProjectedShipment) OVER (PARTITION BY ID ORDER BY LinkDate) NewInventory
FROM tablename;
See the demo.
CodePudding user response：
It works with just SUM() OVER
SELECT *,
SUM(Inventory Receipt - ProjectedShipment) OVER (PARTITION BY ID ORDER BY LinkDate) NewInventory
FROM tablename;