Forecasting with LAG() - NULL issue

Copper Contributor

Forecasting with LAG() - NULL issue

Some Value     Some Change     Other Value

-------------     ---------------     --------------

x                       a%                      41000

y                       b%                      40750

z                       c%                       NULL

m                     d%                       NULL

k                      f%                        NULL

I have data as shown. When the Other Value is NULL, I want to take the LAG value for Other Value and multiple it by the current rows Some Change Column. When I use LAG, it only populates the first NULL. When I get to the next row, LAG returns NULL but I want it to return what was previously calculated multiplied by the next percent in the Some Change column.

For example:

1) 40750 x c%  (This works with LAG but the rest do not)

2) (40750 x c%) x d%

3) ((40750 x c%) x d%) x f%

Is there a way to do this inline without populating a table and looping over the NULLs?

Sample code to work with:

``````DROP TABLE IF EXISTS #LAGISSUE;

CREATE TABLE #LAGISSUE(
ROWID INT,
SOMEVALUE INT,
PercentChange FLOAT,
OtherValue int
)

INSERT INTO #LAGISSUE
SELECT 1,40000, .98254, 41000;

INSERT INTO #LAGISSUE
SELECT 2,41000, .9654, 40750;

INSERT INTO #LAGISSUE
SELECT 3, 42000, .92012,  NULL;

INSERT INTO #LAGISSUE
SELECT 4, 43000, .9357, NULL;

INSERT INTO #LAGISSUE
SELECT 5, 43000, .8976, NULL;

SELECT SomeValue, PercentChange, OtherValue,
CASE WHEN OtherValue IS NULL THEN

ROUND(LAG(OtherValue) OVER(ORDER BY ROWID) * PercentChange,0)

END AS ForecastedValue
FROM #LAGISSUE;

DROP TABLE IF EXISTS #LAGISSUE;``````

SQL gives the following results. How can I get row 4 to be 37495 * .9357 and so on?

0 Replies