Getting YoY or QoQ results

DimQuarter

quat Prev_Quat LastYear_Quat
2017-Q1 2016-Q4 2016-Q1
2017-Q2 2017-Q1 2016-Q2
2017-Q3 2017-Q2 2016-Q3
2017-Q4 2017-Q3 2016-Q4
2018-Q1 2017-Q4 2017-Q1
2018-Q2 2018-Q1 2017-Q2

use Tesla_DWH_STG
create table sales
( quart CHAR(7),
Sales INT,
Volume INT )

insert into sales
select '2018-Q1', 12000, 60 union
select '2017-Q4', 80000,55 union
select '2017-Q3', 76000,50 union
select '2017-Q2', 60000,40 union
select '2017-Q1', 50000,30

how can i get the Quarter,Sales, Volume, Current- Prev quarter Sales, current - last year Sale

CREATE TABLE #sales
   ( quart  char(7)
   , Sales  int
   , Volume int
   ) ;

INSERT INTO #sales
VALUES ('2018-Q1', 12000, 60)
     , ('2017-Q4', 80000, 55)
     , ('2017-Q3', 76000, 50)
     , ('2017-Q2', 60000, 40)
     , ('2017-Q1', 50000, 30) ;

SELECT
   s.quart
 , s.Sales
 , Sum(s.Sales) OVER ( ORDER BY s.quart ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )  SalesRt
 , s.Volume
 , Sum(s.Volume) OVER ( ORDER BY s.quart ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) VolumeRt
 , Sum(s.Volume) OVER ( ORDER BY Left(s.quart, 4) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) VolumeYtd
 , Sum(s.Sales) OVER ( ORDER BY Left(s.quart, 4) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) SalesYtd
FROM
   #sales s
ORDER BY
   s.quart ;

DROP TABLE #sales ;

image

1 Like

How to calculate the %age here.
YoY %age, QoQ%
I was trying a join route..by pre-calculating prev quarter and last year .

so that i can also coalesce a number if its null. Example. if sale is missing using another column..