I have multiple transaction records in table A like this:

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.



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

    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
    Date_Column BETWEEN @SearchStartDate AND @SearchEndDate

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.

  • 1
    The ELSE Null is redundant as that is the default anyway. – Martin Smith Jul 18 '16 at 18:43

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.

              , [CTE_FEB].[CUSTOMER]
              , [CTE_MAR].[CUSTOMER]....) AS [Customer]
,[CTE_JAN].[OrderCount] AS [Jan]
,[CTE_FEB].[OrderCount] AS [Feb]

....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.

  • 1
    This helped me better understand the solution. Thank you. – Jim Barr Jul 18 '16 at 19:24
  • 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. – MguerraTorres Jul 18 '16 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.