Help with dates and PIVOT

Transact-SQL
1

I created the following:

select 
  my_user, 
  [1] AS JAN,
  [2] AS FEB,
  [3] AS MAR,
  [4] AS APR,
  [5] AS MAY,
  [6] AS JUM,
  [7] AS JUL,
  [8] AS AUG,
  [9] AS SEP,
  [10] AS OCT,
  [11] AS NOV, 
  [12] AS DEC  
from
(
  SELECT MONTH(DOP) AS my_month,(LAST_NAME + ', '+ FIRST_NAME) as my_user
  FROM VOUCHERS INNER JOIN
  TRANS_MEMBERS ON VOUCHERS.TRANS_MEM_ID =  TRANS_MEMBERS.TRANS_MEMBER_ID   
) AS t
PIVOT (
  COUNT(my_month)
  FOR my_month IN([1], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[12])
) as p
ORDER BY my_user

which generates the following

my_user JAN FEB MAR APR MAY JUM JUL AUG SEP OCT NOV DEC
ABERCROMBIE, SARAH 0 0 0 0 0 0 0 0 5 5 0 0
ALLEN, MURIEL 0 0 0 0 0 0 0 0 5 0 0 0
ANDERSON, SANDRA 0 0 0 0 0 0 0 0 5 0 0 0
ANDERSON, WILMA RUTH 0 0 0 0 0 0 0 0 0 5 0 0
BARNES, MARY LOU 0 0 0 0 0 0 0 0 0 0 6 0
BISHOP, BETTY 0 0 0 0 0 0 0 0 0 5 0 0
GERSTUNG, EDITH 0 0 0 0 0 0 0 0 0 10 0 0

My issue is that the data is not just for one year. So what I need is for the headers to show JAN 15, FEB 15....JAN 16, etc

So I haven't come up with a way to generate those results.

Table data looks like this

40 12 2015-10-24 10.0000 False NULL
41 111 2015-11-01 10.0000 False NULL
42 111 2015-11-01 10.0000 False NULL
43 111 2015-11-01 10.0000 False NULL
44 111 2015-11-01 10.0000 False NULL
45 111 2016-11-01 10.0000 False NULL
46 111 2016-11-01 10.0000 False NULL

All help will be appreciated.

2

Try this:

select t.my_user, 
      ,p.[201401] AS JAN2014,
      ,p.[201402] AS FEB2014
      ,p.[201403] AS MAR2014
      ,p.[201404] AS APR2014
      ,p.[201405] AS MAY2014
      ,p.[201406] AS JUN2014
      ,p.[201407] AS JUL2014
      ,p.[201408] AS AUG2014
      ,p.[201409] AS SEP2014
      ,p.[201410] AS OCT2014
      ,p.[201411] AS NOV2014
      ,p.[201412] AS DEC2014
      ,p.[201501] AS JAN2015
      ,p.[201502] AS FEB2015
      ,p.[201503] AS MAR2015
      ,p.[201504] AS APR2015
      ,p.[201505] AS MAY2015
      ,p.[201506] AS JUN2015
      ,p.[201507] AS JUL2015
      ,p.[201508] AS AUG2015
      ,p.[201509] AS SEP2015
      ,p.[201510] AS OCT2015
      ,p.[201511] AS NOV2015
      ,p.[201512] AS DEC2015
  from (select year(v.dop)*100+month(v.dop) as my_yearmonth
              ,m.last_name+', '+m.first_name as my_user
          from vouchers as v
               inner join trans_members as m
                       on v.trans_mem_id=m.trans_member_id
         where v.dop>=cast('20140101' as date)
           and v.dop<cast('20160101' as date)
       ) as t
 pivot (count(my_yearmonth)
   for  my_yearmonth in([201401]
                       ,[201402]
                       ,[201403]
                       ,[201404]
                       ,[201405]
                       ,[201406]
                       ,[201407]
                       ,[201408]
                       ,[201409]
                       ,[201410]
                       ,[201411]
                       ,[201412]
                       ,[201501]
                       ,[201502]
                       ,[201503]
                       ,[201504]
                       ,[201505]
                       ,[201506]
                       ,[201507]
                       ,[201508]
                       ,[201509]
                       ,[201510]
                       ,[201511]
                       ,[201512]
                       )
       ) as p
 order by t.my_user
;
3

That worked great. I just need one more thing.

Totals by user.

4

Add this to the select section:

      ,p.[201401]
      +p.[201402]
      +p.[201403]
      +p.[201404]
      +p.[201405]
      +p.[201406]
      +p.[201407]
      +p.[201408]
      +p.[201409]
      +p.[201410]
      +p.[201411]
      +p.[201412]
      +p.[201501]
      +p.[201502]
      +p.[201503]
      +p.[201504]
      +p.[201505]
      +p.[201506]
      +p.[201507]
      +p.[201508]
      +p.[201509]
      +p.[201510]
      +p.[201511]
      +p.[201512] AS Total
5

Sorry. One more question. How about column totals. So far it's working great

6

Try this:

with cte
  as (select t.my_user, 
            ,p.[201401] as JAN2014,
            ,p.[201402] as FEB2014
            .
            .
            .
            ,p.[201511] as NOV2015
            ,p.[201512] as DEC2015
            ,p.[201401]
            +p.[201402]
            .
            .
            .
            +p.[201511]
            +p.[201512] as Total
        from (select year(v.dop)*100+month(v.dop) as my_yearmonth
                    ,m.last_name+', '+m.first_name as my_user
                from vouchers as v
                     inner join trans_members as m
                             on v.trans_mem_id=m.trans_member_id
               where v.dop>=cast('20140101' as date)
                 and v.dop<cast('20160101' as date)
             ) as t
       pivot (count(my_yearmonth)
         for  my_yearmonth in([201401]
                             ,[201402]
                             .
                             .
                             .
                             ,[201511]
                             ,[201512]
                             )
             ) as p
     )
select *
  from (select *
          from cte
        union all
        select 'Total' as my_user
              ,sum(JAN2014) as JAN2014
              ,sum(FEN2014) as FEB2014
              .
              .
              .
              ,sum(NOV2015) as NOV2015
              ,sum(NOV2015) as NOV2015
              ,sum(Total) as Total
          from cte
       ) as a
 order by case when my_user='Total' then 1 else 0 end
         ,my_user
;