Select Unique Customers per month for last 2 years..

Question

Select Unique Customers per month for last 2 years..

michaeleaton 36224

SSC Veteran

Points: 265
More actions
September 10, 2014 at 2:05 pm

#397326

Hey, so I have the following SQL Query:
SELECT COUNT(DISTINCT CustomerID) AS ActiveCustomers, DATENAME(MONTH, PaymentDate) + ' ' + DATENAME(YEAR, PaymentDate) AS [Period]
FROM dbo.[Order]
WHERE DATENAME(YEAR, PaymentDate) > 2012
GROUP BY DATENAME(MONTH, PaymentDate), DATENAME(YEAR, PaymentDate)
ORDER BY [Period] desc
Gives me:
ActiveCustomersPeriod
173September 2014
409September 2013
430October 2013
451November 2013
444May 2014
427May 2013
435March 2014
442March 2013
405June 2014
Besides the active figures being potentially incorrect, I also want to show a month even if there are no 'active customers'
As for the active figures, for the month of September 2013 it should be 380, not 409 when using this query:
SELECT COUNT(DISTINCT (CustomerID))
FROM [dbo].[Order]
WHERE PaymentDate >= '2013-09-01' AND PaymentDate <= '2013-09-30'
Can anyone offer any tips?
Thanks,
Michael

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

michaeleaton 36224 (9/10/2014)
Hey, so I have the following SQL Query:
SELECT COUNT(DISTINCT CustomerID) AS ActiveCustomers, DATENAME(MONTH, PaymentDate) + ' ' + DATENAME(YEAR, PaymentDate) AS [Period]
FROM dbo.[Order]
WHERE DATENAME(YEAR, PaymentDate) > 2012
GROUP BY DATENAME(MONTH, PaymentDate), DATENAME(YEAR, PaymentDate)
ORDER BY [Period] desc
Gives me:
ActiveCustomersPeriod
173September 2014
409September 2013
430October 2013
451November 2013
444May 2014
427May 2013
435March 2014
442March 2013
405June 2014
Besides the active figures being potentially incorrect, I also want to show a month even if there are no 'active customers'
As for the active figures, for the month of September 2013 it should be 380, not 409 when using this query:
SELECT COUNT(DISTINCT (CustomerID))
FROM [dbo].[Order]
WHERE PaymentDate >= '2013-09-01' AND PaymentDate <= '2013-09-30'
Can anyone offer any tips?
Thanks,
Michael

You need to be careful about wrapping your column names in a function in joins and where predicates. This renders your query nonSARGable. Instead of WHERE DATENAME(YEAR, PaymentDate) > 2012 you should use:

WHERE PaymentDate > '2012-01-01'

If you want to show months when there are no active customers you will need to use a table that has those rows as the base table for your query. A numbers or tally table is often used for this. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

On your query for September you have a common logic error. PaymentDate <= '2013-09-30'. This will exclude any rows on the 30th of September if the timestamp is anything greater than midnight. You should change that to < '20130-10-01'.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 2

michaeleaton 36224 (9/10/2014)
Hey, so I have the following SQL Query:
SELECT COUNT(DISTINCT CustomerID) AS ActiveCustomers, DATENAME(MONTH, PaymentDate) + ' ' + DATENAME(YEAR, PaymentDate) AS [Period]
FROM dbo.[Order]
WHERE DATENAME(YEAR, PaymentDate) > 2012
GROUP BY DATENAME(MONTH, PaymentDate), DATENAME(YEAR, PaymentDate)
ORDER BY [Period] desc
Gives me:
ActiveCustomersPeriod
173September 2014
409September 2013
430October 2013
451November 2013
444May 2014
427May 2013
435March 2014
442March 2013
405June 2014
Besides the active figures being potentially incorrect, I also want to show a month even if there are no 'active customers'
As for the active figures, for the month of September 2013 it should be 380, not 409 when using this query:
SELECT COUNT(DISTINCT (CustomerID))
FROM [dbo].[Order]
WHERE PaymentDate >= '2013-09-01' AND PaymentDate <= '2013-09-30'
Can anyone offer any tips?
Thanks,
Michael

If you don't already have one, consider creating a Calendar table in your database. A Calendar table is exactly what it sounds like; it's a table that contains a row for each date, along with other information about that date. For example, in my own reporting database, each row contains the date, the fiscal quarter, fiscal year, year, month, week, day of week, work day, and a few other bits of information.

Then, it's simply a matter of writing a query that pulls the dates you want from the Calendar table, and left joining that to the query where you're actually getting your data. This way, your results include all of the dates in your range whether there are customer id's or not.

If you don't have a Calendar table, you can try something like the following to make one on the fly... the point is, you have a set of dates (in your case months) that you are using, rather than your actual data, if you want to include months that do not appear in your data:

;with years as

(

select '2012' as Yr union all

select '2013' union all

select '2014' union all

select '2015'

)

,months as

(

select 1 as MoNo, 'January' as Mo union all

select 2 as MoNo, 'February' as Mo union all

select 3 as MoNo, 'March' as Mo union all

select 4 as MoNo, 'April' as Mo union all

select 5 as MoNo, 'May' as Mo union all

select 6 as MoNo, 'June' as Mo union all

select 7 as MoNo, 'July' as Mo union all

select 8 as MoNo, 'August' as Mo union all

select 9 as MoNo, 'September' as Mo union all

select 10 as MoNo, 'October' as Mo union all

select 11 as MoNo, 'November' as Mo union all

select 12 as MoNo, 'December' as Mo

)

,calendar as

(

select

Yr,

Yr + MoNo as calSort,

Mo + ' ' + Yr as calPeriod

from

years

cross join months

)

,yourQuery as

(

SELECT COUNT(DISTINCT CustomerID) AS ActiveCustomers, DATENAME(MONTH, PaymentDate) + ' ' + DATENAME(YEAR, PaymentDate) AS [Period]

FROM dbo.orders

GROUP BY DATENAME(MONTH, PaymentDate), DATENAME(YEAR, PaymentDate)

)

select

a.calPeriod,

coalesce(b.ActiveCustomers, 0) as ActiveCustomers

from

calendar a

left outer join yourQuery b on a.calPeriod = b.Period

where

a.Yr > 2012

order by

a.calSort

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

months as

(

select 1 as MoNo, 'January' as Mo union all

select 2 as MoNo, 'February' as Mo union all

select 3 as MoNo, 'March' as Mo union all

select 4 as MoNo, 'April' as Mo union all

select 5 as MoNo, 'May' as Mo union all

select 6 as MoNo, 'June' as Mo union all

select 7 as MoNo, 'July' as Mo union all

select 8 as MoNo, 'August' as Mo union all

select 9 as MoNo, 'September' as Mo union all

select 10 as MoNo, 'October' as Mo union all

select 11 as MoNo, 'November' as Mo union all

select 12 as MoNo, 'December' as Mo

)

Really?!?!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Alan.B (9/10/2014)
months as ( select 1 as MoNo, 'January' as Mo union all select 2 as MoNo, 'February' as Mo union all select 3 as MoNo, 'March' as Mo union all select 4 as MoNo, 'April' as Mo union all select 5 as MoNo, 'May' as Mo union all select 6 as MoNo, 'June' as Mo union all select 7 as MoNo, 'July' as Mo union all select 8 as MoNo, 'August' as Mo union all select 9 as MoNo, 'September' as Mo union all select 10 as MoNo, 'October' as Mo union all select 11 as MoNo, 'November' as Mo union all select 12 as MoNo, 'December' as Mo
)
Really?!?!

I would probably do this differently myself but I don't see what is so bad about this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

Sean Lange (9/11/2014)
Alan.B (9/10/2014)
months as ( select 1 as MoNo, 'January' as Mo union all select 2 as MoNo, 'February' as Mo union all select 3 as MoNo, 'March' as Mo union all select 4 as MoNo, 'April' as Mo union all select 5 as MoNo, 'May' as Mo union all select 6 as MoNo, 'June' as Mo union all select 7 as MoNo, 'July' as Mo union all select 8 as MoNo, 'August' as Mo union all select 9 as MoNo, 'September' as Mo union all select 10 as MoNo, 'October' as Mo union all select 11 as MoNo, 'November' as Mo union all select 12 as MoNo, 'December' as Mo
)
Really?!?!
I would probably do this differently myself but I don't see what is so bad about this.

Not the most constructive response; I was having a grouchy SQL guy moment. I was thinking something like this would be cleaner:

WITH months AS

(

SELECT n AS MoNo, datename(m,dateadd(m,n-1,'1/1/0')) AS Mo

FROM (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) t(n)

)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Alan.B (9/11/2014)
Sean Lange (9/11/2014)
Alan.B (9/10/2014)
months as ( select 1 as MoNo, 'January' as Mo union all select 2 as MoNo, 'February' as Mo union all select 3 as MoNo, 'March' as Mo union all select 4 as MoNo, 'April' as Mo union all select 5 as MoNo, 'May' as Mo union all select 6 as MoNo, 'June' as Mo union all select 7 as MoNo, 'July' as Mo union all select 8 as MoNo, 'August' as Mo union all select 9 as MoNo, 'September' as Mo union all select 10 as MoNo, 'October' as Mo union all select 11 as MoNo, 'November' as Mo union all select 12 as MoNo, 'December' as Mo
)
Really?!?!
I would probably do this differently myself but I don't see what is so bad about this.
Not the most constructive response; I was having a grouchy SQL guy moment. I was thinking something like this would be cleaner:
WITH months AS
(
SELECT n AS MoNo, datename(m,dateadd(m,n-1,'1/1/0')) AS Mo
FROM (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) t(n)
)

hehe been there plenty of times myself. That '1/1/0' really threw me. That will not always work because it is a shortcut based on dateformat settings. Wouldn't it be safer to just use 0?

My personal preference is to use that datepart name instead of the abbreviation. That is one that Jeff Moden and I went back and forth on when he told me that any sql person worth their salt should know them. I am not terribly smart and can't remember those shortcuts because my head is already full of other useless information (and some rocks). 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 7

Alan.B (9/11/2014)
Sean Lange (9/11/2014)
Alan.B (9/10/2014)
months as ( select 1 as MoNo, 'January' as Mo union all select 2 as MoNo, 'February' as Mo union all select 3 as MoNo, 'March' as Mo union all select 4 as MoNo, 'April' as Mo union all select 5 as MoNo, 'May' as Mo union all select 6 as MoNo, 'June' as Mo union all select 7 as MoNo, 'July' as Mo union all select 8 as MoNo, 'August' as Mo union all select 9 as MoNo, 'September' as Mo union all select 10 as MoNo, 'October' as Mo union all select 11 as MoNo, 'November' as Mo union all select 12 as MoNo, 'December' as Mo
)
Really?!?!
I would probably do this differently myself but I don't see what is so bad about this.
Not the most constructive response; I was having a grouchy SQL guy moment. I was thinking something like this would be cleaner:
WITH months AS
(
SELECT n AS MoNo, datename(m,dateadd(m,n-1,'1/1/0')) AS Mo
FROM (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) t(n)
)

I was going more for "easy for a newbie to understand" than for clean... :b