Last active
July 1, 2020 23:08
-
-
Save jingyang-li/1b133a0f4a8913dde19c4bb6bb07ffcc to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Create Table #empSales | |
( | |
employeename varchar(100) | |
,saleamt decimal(10,2) | |
,saleMonth varchar(100) | |
) | |
Insert Into #empSales VALUES | |
('James', '1.00', 'January') | |
,('Richard', '3.28', 'January') | |
,('Barb', '4.13', 'January') | |
,('James', '2.00', 'February') | |
,('Richard', '1.28', 'February') | |
,('Barb', '4.00', 'February') | |
,('James', '21.00', 'March') | |
,('Richard', '13.28', 'March') | |
,('Barb', '2.13', 'March') | |
;with MonthAgg as ( | |
select ISNULL(employeename,'Total') employeename | |
, Sum(Case when saleMonth='January' then saleAmt else null end ) JanuaryAmt | |
, Sum(Case when saleMonth='February' then saleAmt else null end ) FebruaryAmt | |
, Sum(Case when saleMonth='March' then saleAmt else null end ) MarchAmt | |
--.... | |
FROM #empSales | |
GROUP BY employeeName WITH ROLLUP) | |
, mycteSum as ( | |
select saleMonth , Sum(saleAmt) Amt | |
FROM #empSales | |
GROUP BY saleMonth | |
) | |
,mycteYTD as ( | |
select saleMonth , sum(Amt) Over(order by Cast(saleMonth+ ' 01, 1900' as date) ) YTD | |
from mycteSum) | |
Select employeeName,JanuaryAmt, FebruaryAmt,MarchAmt from MonthAgg | |
UNION ALL | |
Select 'YTD--' as employeeName, | |
max(Case when saleMonth='January' then YTD else null end ) JanuaryYTD | |
, max(Case when saleMonth='February' then YTD else null end ) FebruaryYTD | |
, max(Case when saleMonth='March' then YTD else null end ) MarchYTD | |
--... | |
from mycteYTD | |
drop Table #empSales |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
A sample code from MSDN question |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment