Skip to content

Instantly share code, notes, and snippets.

@jingyang-li
Last active July 1, 2020 23:08
Show Gist options
  • Save jingyang-li/1b133a0f4a8913dde19c4bb6bb07ffcc to your computer and use it in GitHub Desktop.
Save jingyang-li/1b133a0f4a8913dde19c4bb6bb07ffcc to your computer and use it in GitHub Desktop.
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
A sample code from MSDN question
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment