Distinct Customer Number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Distinct Customer Number

Author

Topic

SQL_Novice
Starting Member

2 Posts

Posted - 2008-08-11 : 01:14:42

My ID should tell the level of knowledge, so please bear with me if the question sounds silly :).
I'm trying to write a code on SQL 2000, where I can retrieve the count of new customer (Month on Month).
The code I could manage is

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), Order_Date, 6), 6), ' ', '-') AS [Month],
COUNT(DISTINCT(Customer_Number)) as '#Customers'
FROM mytable
GROUP by Order_Date
ORDER by 'Month'

The challenge I'm having is that a Customer_Number may reflect in more than a month. Since the idea is to find the count of new customers, the Customer_Number which has been counted in one month should not be figure again.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 01:31:58

may be this

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), t.Order_Date, 6), 6), ' ', '-') AS [Month],
COUNT(DISTINCT(Customer_Number)) as '#Customers'
FROM mytable t
WHERE t.Customer_Number NOT IN 
(SELECT Customer_Number FROM mytable
WHERE Order_Date< DATEADD(m,DATEDIFF(m,0,t.Order_Date),0))
GROUP by t.Order_Date
ORDER by 'Month'

SQL_Novice
Starting Member

2 Posts

Posted - 2008-08-11 : 01:45:22

You guys rock!! Thanks Visakh for the help. It works.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-11 : 03:16:46

Also dont use single quotes around the column names

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 03:23:18

This may be simpler and faster?

SELECT		DATEADD(MONTH, DATEDIFF(MONTH, '19000101', x.FirstOrder), '19000101') AS theMonth,
		COUNT(*) AS NewCustomers
FROM		(
			SELECT		MIN(Order_Date) AS FirstOrder
			FROM		MyTable
			GROUP BY	Customer_Number
		) AS x
GROUP BY	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', x.FirstOrder), '19000101')
ORDER BY	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', x.FirstOrder), '19000101')

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources