How to get previous quarter total (SQL)

Analytics
Fan

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
3 REPLIES
Highlighted
Fan

Re: How to get previous quarter total (SQL)

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

Junior Contributor

Re: How to get previous quarter total (SQL)

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
Fan

Re: How to get previous quarter total (SQL)

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