Help with Analytic Function

General
Enthusiast

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

Tags (1)
2 REPLIES
Senior Apprentice

Re: Help with Analytic Function

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
Enthusiast

Re: Help with Analytic Function

Thank you Dieter. I really appreciate your quick response.