10/07/2015

T- SQL Real Time Scenarios 2

In this scenario, we have one table, where we need to generate a pivot for showing month wise transaction counts for each category:
Source data:

Date1
Category
Amount
10/1/2015
ABC
1000
2/1/2015
DEF
500
5/1/2015
GHI
800
2/10/2015
DEF
700
3/1/2015
ABC
1100
3/1/2015
XXX
1100
1/1/2014
XXX
1000

 Below desired output need to generate by above data:

Category
Dec-15
Nov-15
Oct-15
Sep-15
Aug-15
Jul-15
Jun-15
May-15
Apr-15
Mar-15
Feb-15
Jan-15
ABC
0
0
0
0
0
0
0
0
0
1
0
1
DEF
0
0
0
0
0
0
0
0
0
0
2
0
GHI
1
0
0
0
0
0
0
0
0
0
0
0
XXX
0
0
0
0
0
0
0
0
0
1
0
1

So first solution comes in the mind is to use pivot function of SQL but by pivot function column will be coming for only those dates which are available in the data as:

Category
Dec-15
Mar-15
Feb-15
Jan-15
ABC
0
1
0
1
DEF
0
0
2
0
GHI
1
0
0
0
XXX
0
1
0
1

But we have to show all months, either the data is available in the table or not, for example we need to show 0 as count of transaction for those month which are not available in the table (from Nov to Apr, data is not available in the table).
So First I have designed a T-SQL query by using case statement to generate such output as:

First I have created a temp table as:
create table #temp1

 (

     date1 datetime,

     category varchar(3),

     amount money

 )

 insert into #temp1 values ('10/1/2015', 'ABC', 1000.00)

 insert into #temp1 values ('2/1/2015', 'DEF', 500.00)

 insert into #temp1 values ('5/1/2015', 'GHI', 800.00)

 insert into #temp1 values ('2/10/2015', 'DEF', 700.00)

 insert into #temp1 values ('3/1/2015', 'ABC', 1100.00)

 insert into #temp1 values ('3/1/2015', 'XXX', 1100.00)

 insert into #temp1 values ('1/1/2014', 'XXX', 1000.00)

SELECT * FROM #temp1

Below T-SQL query is providing complete pivot for all months for showing count of transactions for each category as:

Select Category,

Sum(CASE WHEN DATEPART(mm,date1)=1 THEN 1 ELSE 0 END) as 'Jan-15',

Sum(CASE WHEN DATEPART(mm,date1)=2 THEN 1 ELSE 0 END) as 'Feb-15',

Sum(CASE WHEN DATEPART(mm,date1)=3 THEN 1 ELSE 0 END) as 'Mar-15',

Sum(CASE WHEN DATEPART(mm,date1)=4 THEN 1 ELSE 0 END) as 'Apr-15',

Sum(CASE WHEN DATEPART(mm,date1)=5 THEN 1 ELSE 0 END) as 'May-15',

Sum(CASE WHEN DATEPART(mm,date1)=6 THEN 1 ELSE 0 END) as 'Jun-15',

Sum(CASE WHEN DATEPART(mm,date1)=7 THEN 1 ELSE 0 END) as 'Jul-15',

Sum(CASE WHEN DATEPART(mm,date1)=8 THEN 1 ELSE 0 END) as 'Aug-15',

Sum(CASE WHEN DATEPART(mm,date1)=9 THEN 1 ELSE 0 END) as 'Sep-15',

Sum(CASE WHEN DATEPART(mm,date1)=10 THEN 1 ELSE 0 END) as 'Oct-15',

Sum(CASE WHEN DATEPART(mm,date1)=11 THEN 1 ELSE 0 END) as 'Nov-15',

Sum(CASE WHEN DATEPART(mm,date1)=12 THEN 1 ELSE 0 END) as 'Dec-15'

From  #temp1

Group BY Category

Below is pivot query to generate same result:

SELECT *


        FROM


        (

         SELECT

         CONVERT(VARCHAR(11), LEFT(CONVERT(VARCHAR(11), DATE1,100),3)+'-'+RIGHT(CONVERT(VARCHAR(11),DATE1 ,100),2)) AS Date1

        ,Category

        ,amount

         FROM #temp1

         GROUP BY 

         category

        ,amount

        ,CONVERT(VARCHAR(11), LEFT(CONVERT(VARCHAR(11), DATE1,100),3)+'-'+RIGHT(CONVERT(VARCHAR(11),DATE1 ,100),2))

        ) AS DD

        PIVOT(COUNT(AMOUNT)

        FOR DATE1 IN ([Jan-15],[Feb-15],[Mar-15],[Apr-15],[May-15],[Jun-15],[Jul-15],[Aug-15],[Sep-15],[Oct-15],[Nov-15],[Dec-15]))

        AS PDate
 
If you have any thoughts or suggestion, feel free to post in the below comment section.
Thanks!