All Forums General
nsaikrishna 5 posts Joined 01/12
22 Apr 2014
Help with Analytic Function

I have a situation where I need to do the following:
I have the below information

AYear ASeason AMonth AWeek AUnit ASupplier ASalesArea AAmount
 2013       1      1     1    25        35          1      10
 2013       1      1     2    25        35          1      21
 2013       1      1     3    25        35          1      23
 2013       1      1     4    25        35          1      12
 2013       1      2     1    25        35          1     143
 2013       1      2     2    25        35          1      42
 2013       1      2     3    25        35          1      12
 2013       1      1     1    25        35          2      98
 2013       1      1     2    25        35          2      27
 2013       1      1     3    25        35          2      38
 2013       1      1     4    25        35          2      23
 2013       1      2     1    25        35          2      12
 2013       1      2     2    25        35          2      45
 2013       1      2     3    25        35          2      32

I need to calculate
1. Season to date Cumulative Sum of Amount by Unit, Supplier and Sales Area. 
I am able to get to this using the following

SUM(AAMOUNT) 

OVER (PARTITION BY AYEAR,ASEASON,AUNIT,ASUPPLIER,ASALESAREA

ORDER BY AMONTH, AWEEK

ROWS BETWEEN UNBOUNDED PRECEDING 

AND CURRENT ROW)

 

2. Season to date Cumulative Sum of Amount by Unit and Supplier

I am unable to get to this. If I use the approach above, it randomly sums 1 ASalesArea after the other. But my expectation is, if the partition by + order by yields more than 1 row all of the rows need to have the same amount. 

 

Below is my expected result:

 

Year Season Month Week Dept Vend Loc Amount Season to Date   Season to Date
                                                Sum by Loc      Sum by Vend
2013      1     1    1   25   35   1     10             10              108
2013      1     1    2   25   35   1     21             31              156
2013      1     1    3   25   35   1     23             54              217
2013      1     1    4   25   35   1     12             66              252
2013      1     2    1   25   35   1    143            209              407
2013      1     2    2   25   35   1     42            251              494
2013      1     2    3   25   35   1     12            263              538
2013      1     1    1   25   35   2     98             98              108
2013      1     1    2   25   35   2     27            125              156
2013      1     1    3   25   35   2     38            163              217
2013      1     1    4   25   35   2     23            186              252
2013      1     2    1   25   35   2     12            198              407
2013      1     2    2   25   35   2     45            243              494
2013      1     2    3   25   35   2     32            275              538

 

The last column is what I expect. Any suggestions that you can provide is really appreciated.
Thank you,
Sai

dnoeth 4628 posts Joined 11/04
22 Apr 2014

Hi Sai,
you can't get this in a single pass, you need to use a Derived Table:

SELECT 
   dt.*,
   SUM(A) 
   OVER (PARTITION BY AYEAR,ASEASON,AUNIT,ASUPPLIER, AMONTH, AWEEK) AS SumByVend 
FROM
 (
   SELECT
     ...
     SUM(AAMOUNT) 
     OVER (PARTITION BY AYEAR,ASEASON,AUNIT,ASUPPLIER,ASALESAREA
           ORDER BY AMONTH, AWEEK
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumByLoc
   FROM tab
 ) AS dt

 

Dieter

nsaikrishna 5 posts Joined 01/12
23 Apr 2014

Thank you Dieter. I really appreciate your quick response.

You must sign in to leave a comment.