All Forums Database
td@developer 4 posts Joined 12/09
22 Aug 2014
Moving Differnces without using MDIFF function

 Cycle_Num      Profit_Id        Profit_Rule_Id     Account_NUmber                       MTD_AMT                                         Expected Output
1 201,312                 103              10,053  0000000000000221250884207       23,326.0814                                   23,326.0814
2 201,401                 103              10,053  0000000000000221250884207       379.0471                                        379.0471 MINUS 23,326.0814
3 201,402                 103              10,053  0000000000000221250884207       275.4234                                         275.4234 MINUS 379.0471
4 201,403                 103              10,053  0000000000000221250884207       262.3287                                         Same trend as above
5 201,404                 103              10,053  0000000000000221250884207       245.1935 
6 201,312                 103              10,072  0000000000000221250884207       74.6487        
7 201,401                 103              10,072  0000000000000221250884207       1.4746        
8 201,402                 103              10,072  0000000000000221250884207       1.4624        
9 201,403                 103              10,072  0000000000000221250884207       1.3338        
10 201,404                 103            10,072 0000000000000221250884207       1.1657 
partitioning column should be Cycle_Num , Profit_Rule_Id ,Profit_Id,Account_NUmber. I would not like to use MDIFF function.
Please help with the sql to achieve result mentioned in column Expected Output
Thanks,
 

dnoeth 4628 posts Joined 11/04
22 Aug 2014

According to the manuals:

MDIFF(x, w, y) is equivalent to:
x - SUM(x) OVER (ORDER BY y
    ROWS BETWEEN w PRECEDING AND w PRECEDING)

In your case this should work:

MTD_AMT - COALESCE(MIN(MTD_AMT) 
                   OVER (PARTITION BY Profit_Rule_Id 
                         ORDER BY Cycle_Num
                         ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
                  , 0)

 

 

Dieter

td@developer 4 posts Joined 12/09
25 Aug 2014

Hi Dieter ,
Thanks for your help !
Column MTD_AMT  may have values in which might not be in descending order and i assume in that case it is not going to work.
 

MTD_AMT - COALESCE(MIN(MTD_AMT) 

                   OVER (PARTITION BY Profit_Rule_Id 

                         ORDER BY Cycle_Num

                         ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)

                  , 0)

Please help !
Thanks,

dnoeth 4628 posts Joined 11/04
25 Aug 2014

You don't sort by MTD_AMT, you must sort by your logical sort order (which must be unique), using ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING you get the previous row's value.
Based on your data it looked like you need to sort (not partition) by Cycle_Num.

Dieter

td@developer 4 posts Joined 12/09
25 Aug 2014

Thanks Dieter , for your quick help on this .

You must sign in to leave a comment.