Count number of months

[machumpion]

In a sales table showing Customer, SalesProduct, DollarValue, and OrderDate (yyyy-mm-dd). How can I run a query of the number of months a Customer ordered and not the count of the number of dates?

for example:
Customer 1 ordered on
Oct 1, 2016,
Oct 3 2016,
Oct 5, 2016,
Nov 11 2016
Nov 12 2016

Desired output
2
thanks!

 

[MarkK]

Try something like...

SELECT Count(*) AS CountOfMonths
FROM (
   SELECT DISTINCT Month(OrderDate), Year(OrderDate)
   FROM tSalesTable 
);

 

[machumpion]

Try something like...

SELECT Count(*) AS CountOfMonths
FROM (
   SELECT DISTINCT Month(OrderDate), Year(OrderDate)
   FROM tSalesTable 
);

That worked scrumptiously, thanks! Now, suppose I wish to count the months for each Customer in the Customer table. What would that SQL look like?

I tried to mash it into your code but it's too advanced for me.

 

[MarkK]

...count the months for each Customer in the Customer table.

The Customer table has dates we can count??? You must mean the Order or Sales table. Consider...

SELECT Count(*) AS MonthCount
FROM (
   SELECT DISTINCT Month(OrderDate), Year(OrderDate)
   FROM tSalesTable 
[COLOR="Blue"]   WHERE CustomerID = 1234[/COLOR]
);

See what we're doing there? First--in the subquery--we select the distinct months for a particular customer. Then we count those results in the main query.

 

[machumpion]

The Customer table has dates we can count??? You must mean the Order or Sales table. Consider...

SELECT Count(*) AS MonthCount
FROM (
   SELECT DISTINCT Month(OrderDate), Year(OrderDate)
   FROM tSalesTable 
[COLOR=Blue]   WHERE CustomerID = 1234[/COLOR]
);

See what we're doing there? First--in the subquery--we select the distinct months for a particular customer. Then we count those results in the main query.

Hi Mark,
Thanks for your reply. Unfortunately the table relationships are much more complicated than your code considers. There's a separate customer table, containing CustomerName(foreign key is [Orders].[customer]), Address, and AccountNumber.

I would like to count the months (in the Order table) for each AccountNumber (in the Customer table). The tables are linked by CustomerName.

The reason why I would like to count months for each AccountNumber as opposed to Customer is because the database contains data from different external parties who use different names for the same customer. I used an AccountNumber so one customer with two different spellings get aggregated when I summarize data by AccountNumber .

 

[MarkK]

How many account numbers are there per customer?

 

[machumpion]

How many account numbers are there per customer?

There's only one account number per customer

i.e.

Customer/ AccountNumber
Jack Jones/ 101
Jack. M Jones/ 101
Jack Maury Jones/ 101

Jill Frost/ 102
J. Frost/ 102

Mike Nox/ 103

 

[static]

SELECT a.AccountNumber, Sum(1) as MnthCount, b.mnth
FROM tCustomer a 
INNER JOIN (select customer, monthname(month(orderdate)) & ' ' & year(orderdate) as Mnth from torder) b 
ON a.CustomerName = b.customer
GROUP BY a.AccountNumber, b.mnth

 

[jdraw]

machumpion,
You have said
Unfortunately the table relationships are much more complicated than your code
considers. There's a separate customer table, containing CustomerName
(foreign key is [Orders].[customer]), Address, and AccountNumber..

Please tell us more. Show us your relationships view.
What exactly makes these so complicated?

 

[machumpion]

machumpion,
You have said
Unfortunately the table relationships are much more complicated than your code
considers. There's a separate customer table, containing CustomerName
(foreign key is [Orders].[customer]), Address, and AccountNumber..

Please tell us more. Show us your relationships view.
What exactly makes these so complicated?

Please see attached,

it's more complicated because rather than counting the months for each customer in the Orders table, I'd like to count the months for each AccountNumber in the Stores table.

 

[jdraw]

machumpion,

I don't understand why that makes things complicated. It seems you are just giving a few
"dribs and drabs" of information. Can you tell us in 5-6 lines What your database is about?
What makes your inventory different/special from other inventories. Don't get me wrong -I am fully aware that inventory and stock management is not a trivial subject.
I'd like to see the big picture you are working with to help with context.

Where in Canada are you?

 

[machumpion]

machumpion,

I don't understand why that makes things complicated. It seems you are just giving a few
"dribs and drabs" of information. Can you tell us in 5-6 lines What your database is about?
What makes your inventory different/special from other inventories. Don't get me wrong -I am fully aware that inventory and stock management is not a trivial subject.
I'd like to see the big picture you are working with to help with context.

Where in Canada are you?

High level overview: I sell products to customers. There is a Stores table that contains customer details. Sometimes the same customer will appear twice but under a slightly different spelling (StoreName). These duplicate customers are aggregated with an Accountnumber.

When a customer makes an order. It is recorded in the Orders table. The date of the Order is in OrderMonth (format yyyy-mm-dd). I want to count the number of months each AccountNumber ordered in a given year.

 

[machumpion]

Hey y'all,

I figured it out, the code below counts the unique months from the Orders table and summarizes it by AccountNumber in the Stores table for the year 2016:

SELECT ccsym.accountnumber, Count(*) AS NumDistinctMonths
FROM (SELECT s.accountnumber, year(o.ordermonth) AS yyyy, month(o.ordermonth) AS mm, count(*) AS cnt FROM orders AS o INNER JOIN stores AS s ON o.store = s.storename WHERE year(o.ordermonth)=2016 GROUP BY s.accountnumber, year(o.ordermonth), month(o.ordermonth)) AS ccsym
GROUP BY ccsym.accountnumber;

This is probably extremely advanced, but as a follow up, what if instead, I wanted to count the unique months for the rolling twelve months as opposed to the 2016 calendar year?

 

[MarkK]

First, congrats on getting it worked out! Then, for the rolling 12 months, all you need to do is change the WHERE clause in the subquery. Where you currently do...

WHERE year(o.ordermonth)=2016

...you might do something like...

WHERE o.OrderDate > DateAdd("y", -1, Date())

...so we select records where the order date is greater than today minus one year.
Make sense?

 

[machumpion]

First, congrats on getting it worked out! Then, for the rolling 12 months, all you need to do is change the WHERE clause in the subquery. Where you currently do...

WHERE year(o.ordermonth)=2016

...you might do something like...

WHERE o.OrderDate > DateAdd("y", -1, Date())

...so we select records where the order date is greater than today minus one year.
Make sense?

it didn't work exactly as you wrote it but i was able to use rderDate>Date()-365 to approximate the effect I want. Thanks!