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 ;