General
Enthusiast

## Retain amounts where NULL and divide by number of NULLS

Good day, I'm trying to split the amounts evenly per day, some days are blank and therefore the prior amount should be used / 2 and first amount should be updates, when there are 2 blanks the prior amount should be used / 3 and first amount should be updated ect. please can somone give me some advice, I'm new to Tera Data.

Acc   Date           Code  Amt

1234 2015-04-01 6116  100.00

1234 2015-04-02 6116  300.00

1234 2015-04-03 NULL  NULL

1234 2015-04-04 6116  300.00

1234 2015-04-05 NULL  NULL

1234 2015-04-06 NULL  NULL

1234 2015-04-07 6116  100.00

1234 2015-04-08 6116  100.00

1234 2015-04-09 6116  100.00

I want to achieve this:

Acc   Date            Code  Amt

1234 2015-04-01 6116  100.00

1234 2015-04-02 6116  150.00

1234 2015-04-03 6116  150.00

1234 2015-04-04 6116  100.00

1234 2015-04-05 6116  100.00

1234 2015-04-06 6116  100.00

1234 2015-04-07 6116  100.00

1234 2015-04-08 6116  100.00

1234 2015-04-09 6116  100.00

2 REPLIES
Senior Apprentice

## Re: Retain amounts where NULL and divide by number of NULLS

This should return the expected result (if Code and Amt are both NULL):

`SELECT   Acc, dt,    --get the Code for each group   MAX(Code) OVER (PARTITION BY Acc, grp),   --get the Amt for each group and divide by the number of rows     MAX(Amt) OVER (PARTITION BY Acc, grp)   / COUNT(*) OVER (PARTITION BY Acc, grp)FROM (   SELECT Acc, dt, Code, Amt       -- put NULLs together with the previous row in a group      -- i.e. exactly one amount in each group      ,SUM(CASE WHEN Amt IS NULL THEN 0 ELSE 1 end)        OVER (PARTITION BY Acc              ORDER BY dt              ROWS UNBOUNDED PRECEDING) AS grp   FROM tab ) AS dt`
Enthusiast

## Re: Retain amounts where NULL and divide by number of NULLS

Hi Dieter, this is great code, thanks a mill it really works good.