ash ash - 4 years ago 238
SQL Question

Counting new Customers per Month

I am crazy confused about this for some reason.

Basically, I'm looking for a query that will find the number of new customers per month, since 2010.

I have the customer's email address(email), all orders placed(OrderID), and what date it was placed on(OrderDate). The table is tblOrder.

I know that a "new customer" is: (a) someone who's never ordered before the date/month and (b) who has at least one order after the date/month

I'd want the output to be something like this in the end, with a simpler method being better:

01 02 03 04 05 06 07 08 09 10 11 12
2010 ## ## ## ## ## ## ## ## ## ## ## ##
2011 ## ## ## ## ## ## ## ## ## ## ## ##
2012 ## ## ## ## ## ## ## ## ## ## ## ##





And I was given this to work with, but guys, I'm seriously not a programmer, and it may look simple to some of y'all but it's over my head and not clicking with me at all.

SELECT <customer info>
FROM <customer table>
WHERE (SELECT COUNT(<order info>)
FROM <order table>
WHERE <customer info> = <current customer>
AND <date> < <target date>) = 0
AND (SELECT COUNT(<order info>
FROM <order table>
WHERE <customer info> = <current customer>
AND <date> > <target date>) > 0


I know this isn't valid SQL either. So I don't know what to do with it. And I think it just pulls a list of applicable customers (meaning those who haven't ordered before the inputted month) rather than counting them all up and totaling them like I ultimately want.

Answer Source

Try:

select yr, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
from
(select datepart(month,minDate) mth, datepart(year,minDate) yr, count(*) cnt
 from (select min(OrderDate) minDate, max(OrderDate) maxDate
       from tblOrder
       group by email) sq
 where datediff(month, minDate, maxDate) > 0
 group by datepart(month,minDate), datepart(year,minDate)) src
PIVOT
(max(cnt) 
 for mth in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) ) pvt

SQLFiddle here.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download