CREATE TABLE orders
( order_id int identity(1,1) primary key
, amount numeric(10,2)
, order_dt datetime
) ; INSERT orders
( amount, order_dt ) VALUES
( 10.01, '4-01-2019' )
,( 9.99, '3-16-2019' )
,( 7.01, '1-15-2019' )
,( 23.46, '4-15-2019' )
,( 3.16, '2-14-2019' )
,( 4.13, '2-11-2019' )
,( 12.14, '7-04-2019' )
,( 13.16, '5-20-2019' )
,( 6.10, '8-23-2019' )
,( 1.53, '1-01-2020' )
,( 40.66, '1-18-2020' )
; ;with cte_amt as
( select [amount], year(order_dt) as [yr], yr_amount, month(order_dt) as [mo]
from [orders] o
JOIN ( select year([order_dt]) as [yr], sum([amount]) as [yr_amount] from [orders] group by year([order_dt]) ) as y
ON year([order_dt]) = y.[yr]
)
select yr as [Year], [yr_amount] as [Annual Total]
, coalesce([1] ,0) as [Jan], coalesce([2] ,0) as [Feb], coalesce([3] ,0) as [Mar]
, coalesce([4] ,0) as [Apr], coalesce([5] ,0) as [May], coalesce([6] ,0) as [Jun]
, coalesce([7] ,0) as [Jul], coalesce([8] ,0) as [Aug], coalesce([9] ,0) as [Sep]
, coalesce([10],0) as [Oct], coalesce([11] ,0) as [Nov], coalesce([12],0) as [Dec]
from cte_amt
pivot(sum(amount) for mo in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) as pvt
; |
