All Forums Analytics
ivot 3 posts Joined 10/14
10 Feb 2015
How to get previous quarter total (SQL)

Hello,
I have a table with Customer, Quarter, Month and Sales (montly) figures.
I need to calculate Quarter Sales and Previous Quarter Sales per Customer, while also showing Montly sales (see desired table format below).
For quarter sales I use sum() over partition by

Select

Customer

, Quarter

, Month

, sum(sales) over (partition by Customer, Quarter order by Quarter) as QuarterSales

from TableA

 
 
However, I am not able to find a way to get total for previous quarter. I suppose, I need to remove Quarter from partition by statement, but then I need to select only rows, where Quarter is equal Quarter - 1 somehow. Probably Reset When should help, but I am not able to make it work properly.
Thank you in advance,
Ivan
Table A:

Customer

Quarter

Month

Sales

1

1

1

0

1

1

2

100

1

1

3

200

1

2

4

300

1

2

5

100

1

2

6

50

2

1

1

100

2

1

2

0

2

1

3

100

2

2

4

50

2

2

5

200

2

2

6

300

Output: TableA + Calculated Quarterly totals:

Customer

Quarter

Month

Sales

QuarterSales

PreviousQuarterSales

1

1

1

0

300

 

1

1

2

100

300

 

1

1

3

200

300

 

1

2

4

300

450

300

1

2

5

100

450

300

1

2

6

50

450

300

2

1

1

100

200

 

2

1

2

0

200

 

2

1

3

100

200

 

2

2

4

50

550

200

2

2

5

200

550

200

2

2

6

300

550

200

 

ivot 3 posts Joined 10/14
10 Feb 2015

Sorry, tables do not look nice:
TableA
Customer Quarter Month Sales

1             1          1        0

1             1          2        100

1             1          3        200

1             2          4        300

1             2          5        100

1             2          6        50

2             1          1        100

2             1          2        0

2             1          3        100

2             2          4        50

2             2          5        200

2             2          6        300

 

TableA + Quarter Totals

Customer Quarter Month Sales QuarterSales PreviousQuarterSales

1             1          1        0       300

1             1          2        100    300

1             1          3        200    300

1             2          4        300    450               300

1             2          5        100    450               300

1             2          6        50      450               300

2             1          1        100    200

2             1          2        0       200

2             1          3        100    200

2             2          4        50     550               200

2             2          5        200   550               200

2             2          6        300   550               200

dnoeth 4628 posts Joined 11/04
10 Feb 2015

There's no way to get this result without nesting OLAP-functions.
Assuming there's always a row per customer/quarter:

Select
   Customer
 , Quarter
 , Month
 , QuarterSales
 , sum(QuarterSales)
   over (partition by Customer
         order by Month
         rows between 3 preceding and 3 preceding) as QuarterSales
from
 (
   Select
      Customer
    , Quarter
    , Month
    , sum(sales) over (partition by Customer, Quarter order by Quarter) as QuarterSales
   from TableA
 ) as dt

 

Dieter

ivot 3 posts Joined 10/14
11 Feb 2015

Hi Dieter,
Thank you for a quick reply. I did not think in this direction. I am still learning how you can use partition by approach properly.
This does work, however, I was trying to avoid nesting OLAP-functions.
I just wonder, from performance point of view what is the preferred option to have nested OLAP function or to have a separate table (TableSum) with calculated quarter totals for each customer and then left join to the original table (TableA) on TableSum.Customer = TableA.Customer and TableSum.Quarter = TableA.Quarter-1.
So far I used second approach (left join) instead of nested OLAP-function.
I will test partition by approach to see the benefits.
Thank you again.
Ivan

You must sign in to leave a comment.