Search code examples
sqlteradataolapsliding-window

Difference between current and previous column using OLAP functions

I was asked to create a report (using Teradata SQL OLAP functions) as below 

    EMPL_ID | perd_end_d | pdct_I | Year to date sal Amnt | Diff in sale amnt from Prev month
-------------------------------------------------------------------------------------------

I was given the following "sales" dataset and I have to calculate "Year to date sale amount" and "difference in crrent and previous month's sale amount" 

empl_id| perd_end_d | pdct_I|sale_amnt|
----------------------------------------
E1001  | 31-01-2010 | P2003 | 2,03    |
E1003  | 31-01-2010 | P2015 | 44      |
E1003  | 31-01-2010 | P2004 | 67,6    |
E1001  | 31-01-2010 | P2002 | 135     |
E1003  | 31-01-2010 | P2003 | 545     |
E1001  | 31-01-2010 | P2001 | 1,00    |
E1002  | 31-01-2010 | P2005 | 23      |
E1002  | 31-01-2010 | P2007 | 343     |
E1006  | 28-02-2010 | P2005 | 34      |
E1006  | 28-02-2010 | P2004 | 43      |
E1001  | 28-02-2010 | P2003 | 54      |
E1001  | 28-02-2010 | P2002 | 878     |
E1003  | 28-02-2010 | P2008 | 434     |
E1001  | 28-02-2010 | P2001 | 66      |
E1007  | 28-02-2010 | P2009 | 455     |
E1007  | 28-02-2010 | P2009 | 4,54    |
E1003  | 28-02-2010 | P2007 | 56      |
E1008  | 28-02-2010 | P2009 | 786     |
E1010  | 31-01-2011 | P2001 | 300     |
E1001  | 31-01-2011 | P2002 | 200     |
E1009  | 31-01-2011 | P2003 | 100     |
E1011  | 31-01-2012 | P2004 | 700     |
E1002  | 31-01-2012 | P2005 | 400     |
E1011  | 31-01-2012 | P2003 | 600     |
E1002  | 31-01-2012 | P2007 |  500    |
---------------------------------------

I want something like below

empl_id| perd_end_d | pdct_I|sale_amnt| diff(ur_mnt_sal - prev_mnt_sal)
-------------------------------------------------------------------------
E1001  | 31-01-2010 | P2003 | 2,03    | 203 -- or may be null
E1003  | 31-01-2010 | P2015 | 44      | 159
E1003  | 31-01-2010 | P2004 | 67,6    | 632
E1001  | 31-01-2010 | P2002 | 135     | 541
E1003  | 31-01-2010 | P2003 | 545     | 410
...

So far I managed to find the required result but it looks ugly, how can I improve the following solution.

SELECT perd_end_d
    , pdct_I
    , sale_amnt
    , ABS( SUM(sale_amnt) over (partition by perd_end_d
                                order by perd_end_d
                                rows  between 1 preceding and 1 preceding ) 
      - SUM(sale_amnt) over (partition by perd_end_d
                             order by perd_end_d
                             rows  current row ) )"prev_mnt_sal - cur_mnt_sal"                              
from sandbox.sales;

and the resultset is as following

enter image description here

Solution 
  • SELECT perd_end_d
    , pdct_I
    , sale_amnt
    , ABS( min(sale_amnt) over (partition by perd_end_d
                                order by perd_end_d
                                rows  between 1 preceding and 1 preceding ) 
      - sale_amnt) as "prev_mnt_sal - cur_mnt_sal"                              
from sandbox.sales;