I am having Problems trying to use the lag Function to calculate the Previous Value of a row. I Ma Uing the following statement
USE [Site Shortage Excess];
GO
UPDATE [Site Shortage Excess]
SET [Site Shortage Excess].OHBal = COHBal.Prev_OHBal,
[Site Shortage Excess].[OHBal+PO] = COHBal.Prev_OHBal + [Site Shortage Excess].[PO]
FROM [Site Shortage Excess]
LEFT OUTER JOIN (
SELECT *,
IIF(LAG([Period_ID],1,0) OVER (PARTITION BY [Site_Part_ID] ORDER BY [Site_Part_Period_ID]) = 0,
[OH] - [Dmd],
IIF([Site_Part_ID] <> LAG([Site_Part_ID],1,0) OVER (PARTITION BY [Site_Part_ID] ORDER BY [Site_Part_Period_ID]),
[OH] - [Dmd],
LAG([OHBal],1,0) OVER (PARTITION BY [Site_Part_ID] ORDER BY [Site_Part_Period_ID]) - [Dmd])
) AS Prev_OHBal,
LAG([Period_ID],1,0) OVER (PARTITION BY [Site_PART_ID] ORDER BY [Site_Part_Period_ID]) AS Prev_Period_ID,
LAG([Site_Part_ID],1,0) OVER (PARTITION BY [Site_Part_ID] ORDER BY [Site_Part_Period_ID]) AS Prev_Site_Part_ID
FROM [Site Shortage Excess]
--WHERE [Site_Part_ID] IN (1,2)
--ORDER BY [Site_Part_Period_ID]
) AS COHBal
ON [Site Shortage Excess].[Site_Part_Period_ID] = COHBal.[Site_Part_Period_ID];
A running the subquery for only the first Site_Part_ID generates the following results
Site_Part_Period_ID Site_Part_ID Period_ID Site Part Period OH Dmd PO OHBal OHBal+PO Prev_OHBal Prev_Period_ID Prev_Site_Part_ID
1 1 1 CHS 004-9000263 2024-02-26 0.16 0 0 0.16 0.16 0.16 0 0
2 1 2 CHS 004-9000263 2024-03-04 0.16 0 0 0.16 0.16 0.16 1 1
3 1 3 CHS 004-9000263 2024-03-11 0.16 0 0 0.16 0.16 0.16 2 1
4 1 4 CHS 004-9000263 2024-03-18 0.16 0 0 0.16 0.16 0.16 3 1
5 1 5 CHS 004-9000263 2024-03-25 0.16 0 0 0.16 0.16 0.16 4 1
6 1 6 CHS 004-9000263 2024-04-01 0.16 0 0 0.16 0.16 0.16 5 1
7 1 7 CHS 004-9000263 2024-04-08 0.16 0 0 NULL NULL 0.16 6 1
8 1 8 CHS 004-9000263 2024-04-15 0.16 0 0 NULL NULL NULL 7 1
9 1 9 CHS 004-9000263 2024-04-22 0.16 0 0 NULL NULL NULL 8 1
10 1 10 CHS 004-9000263 2024-04-29 0.16 0 0 NULL NULL NULL 9 1
11 1 11 CHS 004-9000263 2024-05-06 0.16 0 0 NULL NULL NULL 10 1
12 1 12 CHS 004-9000263 2024-05-13 0.16 0 0 NULL NULL NULL 11 1
13 1 13 CHS 004-9000263 2024-05-20 0.16 0 0 NULL NULL NULL 12 1
14 1 14 CHS 004-9000263 2024-05-27 0.16 0 0 NULL NULL NULL 13 1
15 1 15 CHS 004-9000263 2024-06-03 0.16 0 0 NULL NULL NULL 14 1
16 2 1 CHS 004-9000328 2024-02-26 1.45 0 0 1.45 1.45 1.45 0 0
17 2 2 CHS 004-9000328 2024-03-04 1.45 0 0 1.45 1.45 1.45 1 2
18 2 3 CHS 004-9000328 2024-03-11 1.45 0 0 1.45 1.45 1.45 2 2
19 2 4 CHS 004-9000328 2024-03-18 1.45 0 0 1.45 1.45 1.45 3 2
20 2 5 CHS 004-9000328 2024-03-25 1.45 0 0 1.45 1.45 1.45 4 2
21 2 6 CHS 004-9000328 2024-04-01 1.45 0 0 1.45 1.45 1.45 5 2
22 2 7 CHS 004-9000328 2024-04-08 1.45 0 0 NULL NULL 1.45 6 2
23 2 8 CHS 004-9000328 2024-04-15 1.45 0 0 NULL NULL NULL 7 2
24 2 9 CHS 004-9000328 2024-04-22 1.45 0 0 NULL NULL NULL 8 2
25 2 10 CHS 004-9000328 2024-04-29 1.45 0 0 NULL NULL NULL 9 2
26 2 11 CHS 004-9000328 2024-05-06 1.45 0.6 0 NULL NULL NULL 10 2
27 2 12 CHS 004-9000328 2024-05-13 1.45 1.2 0 NULL NULL NULL 11 2
28 2 13 CHS 004-9000328 2024-05-20 1.45 0 0 NULL NULL NULL 12 2
29 2 14 CHS 004-9000328 2024-05-27 1.45 0 0 NULL NULL NULL 13 2
30 2 15 CHS 004-9000328 2024-06-03 1.45 0.6 0 NULL NULL NULL 14 2
I would like to now why the system is only calculating correctly the Prev_OHBal for the first 5 rows for each Site_Part_ID and for the rest returns NULL. If you look the values for Prev_Site_Part_ID and Prev_Period_ID are correct
