Moving Differnces without using MDIFF function

Database
td
Fan

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,

4 REPLIES
Senior Apprentice

Re: Moving Differnces without using MDIFF function

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)

td
Fan

Re: Moving Differnces without using MDIFF function

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,

Senior Apprentice

Re: Moving Differnces without using MDIFF function

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.

td
Fan

Re: Moving Differnces without using MDIFF function

Thanks Dieter , for your quick help on this .