SQL Server – How to Create a Summary Grid Query

sql servert-sql

I have multiple transaction records in table A like this:

TXN#  DATE        CUSTOMER
1001  10/05/2015  Cust 1
1002  02/03/2016  Cust 2
1003  02/06/2016  Cust 1
1004  03/25/2016  Cust 3
1005  03/26/2016  Cust 1
1006  03/26/2016  Cust 2
1007  04/06/2016  Cust 3

I would like to create a query or a new table that contains the total number (count) of transactions for each month by CUSTOMER like this:

CUSTOMER  Jan   Feb   Mar   Apr   May ... etc.
Cust 1      0     1     1     0     0 ... etc.
Cust 2      0     1     1     0     0 ... etc.
Cust 3      0     0     2     0     0 ... etc.

I understand a query that gives me counts for a specific time range, but I don't know how to do it for multiple months in one nice result grid.

Is this possible?

I was thinking of just updating the total counts into a table, and manipulating the second table, but I need to bone up on that.

This is the code I'm using to do the current search:

/* Declarations */
DECLARE @SearchStartDate varchar(10);
DECLARE @SearchEndDate   varchar(10);
SET @SearchStartDate = '2016-01-01';
SET @SearchEndDate   = '2016-12-31';

/* Count by customer */
select e.custaccountid as "Company", count (e.custaccountid) as "Orders"
from oriediorder as e  WITH (NOLOCK)
join salestable as s  WITH (NOLOCK)
on (e.dataareaid = s.dataareaid) and (e.salesid = s.salesid) and (e.PONUMBER = s.PONUMBER)
group by e.custaccountid
order by Orders desc;

It requires joins and groupings.

Suggestions?

Best Answer

You should be able to turn your top set of data into the tabular set of data below it by doing something like this:

SELECT
    Customer,
    Count(CASE Month(Date_Column) WHEN 1 THEN [TXN#] ELSE Null END) AS Jan,
    Count(CASE Month(Date_Column) WHEN 2 THEN [TXN#] ELSE Null END) AS Feb,
    Count(CASE Month(Date_Column) WHEN 3 THEN [TXN#] ELSE Null END) AS Mar,
    Count(CASE Month(Date_Column) WHEN 4 THEN [TXN#] ELSE Null END) AS Apr,
    Count(CASE Month(Date_Column) WHEN 5 THEN [TXN#] ELSE Null END) AS May,
    Count(CASE Month(Date_Column) WHEN 6 THEN [TXN#] ELSE Null END) AS Jun,
    Count(CASE Month(Date_Column) WHEN 7 THEN [TXN#] ELSE Null END) AS Jul,
    Count(CASE Month(Date_Column) WHEN 8 THEN [TXN#] ELSE Null END) AS Aug,
    Count(CASE Month(Date_Column) WHEN 9 THEN [TXN#] ELSE Null END) AS Sep,
    Count(CASE Month(Date_Column) WHEN 10 THEN [TXN#] ELSE Null END) AS Oct,
    Count(CASE Month(Date_Column) WHEN 11 THEN [TXN#] ELSE Null END) AS Nov,
    Count(CASE Month(Date_Column) WHEN 12 THEN [TXN#] ELSE Null END) AS Dec
FROM 
    Table_name
WHERE
    Date_Column BETWEEN @SearchStartDate AND @SearchEndDate
GROUP BY 
    Customer;

This works because aggregate functions like SUM or COUNT will not include Null values. The case statement for each month outputs Null if the data is not for the particular month, so the data left being aggregated is only for the one specific month.

Related Question