All Forums General
A212741 7 posts Joined 05/15
15 May 2015
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

dnoeth 4628 posts Joined 11/04
15 May 2015

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

 

Dieter

A212741 7 posts Joined 05/15
16 May 2015

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

You must sign in to leave a comment.