Retain amounts where NULL and divide by number of NULLS

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
Junior Contributor

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.