Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 10 Mar 2008 @ 22:36:38 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   NULL handling in Cumulative Sum function
 
From:   Peter Shvets

Hello,

I have a question on how to handle NULLs enclosed by real data in OLAP function, specifically cumulative sum.

Here is my original data:

     Transid     customer_id        amt
     176265745   5545645555156058   NULL
     176294417   5545645555156058   NULL
     176315567   5545645555156058   -42.51
     176353072   5545645555156058   NULL
     176356373   5545645555156058   -19.99

I use OLAP function to compute cumulative sum:

     SELECT
     -- Sender Gross CB
     zeroifnull(COUNT(amt) OVER (PARTITION BY s.customer_id
                                    ORDER BY s.transid ASC
                                       ROWS BETWEEN UNBOUNDED PRECEDING AND
     CURRENT ROW
                                  )
                )
                  AS cumulative_cnt
     ,zeroifnull(SUM(amt) OVER (PARTITION BY s. customer _id
                                   ORDER BY s.transid ASC
                                     ROWS BETWEEN UNBOUNDED PRECEDING AND
     CURRENT ROW
           )
          )
                  AS cumulative_amt

which produces the dataset below:

     Transid     customer_id        Cumulative_cnt   Cumulative_amt
     176265745   5545645555156058         0               0.00
     176294417   5545645555156058         0               0.00
     176315567   5545645555156058         1             -42.51
     176353072   5545645555156058         1             -42.51
     176356373   5545645555156058         2             -62.50

But I need to exclude a second from the bottom row with amt = NULL with transid = 176353072 but keep the transaction id in the result.

     Transid     customer_id        Cumulative_cnt   Cumulative_amt
     176265745    5545645555156058        0               0.00
     176294417    5545645555156058        0               0.00
     176315567    5545645555156058        1             -42.51
     176353072    5545645555156058        0                  0
     176356373    5545645555156058        2             -62.50

I achieve desired result by separating datasets based on IS NULL and IS NOT NULL, processing them separately and then UNION, but it seems not really elegant.

I am wondering is there a way to handle this logic inside OLAP function?


Thanks a lot in advance,

Peter.



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016