Need to create a "summary grid" query?

Question

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?

Answer 1

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.

• Martin Smith: The ELSE Null is redundant as that is the default anyway.

Answer 2

If you're looking to go Month by month, then use CTE to build the datasets for each month.

So create SELECT queries for each month you're looking at, then push each of those into their own CTE. Each CTE should contain two columns: Customer Number, and Total Orders for the month.

Once you have all of them built, use FULL OUTER JOIN to Join them all together.

SELECT COALESCE([CTE_JAN].[CUSTOMER]
 , [CTE_FEB].[CUSTOMER]
 , [CTE_MAR].[CUSTOMER]....) AS [Customer]
,[CTE_JAN].[OrderCount] AS [Jan]
,[CTE_FEB].[OrderCount] AS [Feb]
FROM [CTE_JAN]
FULL OUTER JOIN [CTE_FEB]
ON [CTE_JAN].[CUSTOMER] = [CTE_FEB].[CUSTOMER]

....One Join per CTE Table

The key to the FULL OUTER JOIN is that if the Customer did not become active until April, their order count will still be represented as NULL for January-March.

--You can also use ISNULL when selecting the order counts if you want 
--to return the data in a cleaner form:
ISNULL([CTE_FEB].[OrderCount],'N/A') AS [Jan]

You can also (and this would probably be the more popular approach) use PIVOT by building a dataset by grouping by MONTH/Customer and then PIVOT to get the Customer/Month in a horizontal fashion.

• Jim Barr: This helped me better understand the solution. Thank you.
• SQLDevDBA: I'm glad I answered, because I was able to see Shane's answer, which is a much faster approach. I'll definitely be using that method in the future.