Select customers - purchases made in all 3 years

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

Select customers - purchases made in all 3 years

Author

Topic

drymnfr
Starting Member

9 Posts

Posted - 2010-04-19 : 17:09:40

Hi,

I need to select ONLY those Customers records who made purchases in ALL 3 years(2007, 2008, 2009), in other words, if a customer has bought merchandise every year in the last 3 years, then I would like to list the customer and all the transactions.

Table structure is as follows:
CustomerID, DatePurchased, ItemID, QtyOrdered

Thanks in advance for your help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-19 : 17:17:11

Here is one way:

SELECT 
	*
FROM 
	Table AS T
INNER JOIN
	(
		SELECT CustomerID
		FROM Table
		WHERE DatePurchased >= '20070101' AND DatePurchased < '20100101'
		GROUP BY CustomerID
		HAVING COUNT(DISTINCT(YEAR(DatePurchased))) = 3
	) AS D
	ON T.CustomerID = D.CustomerID
WHERE 
	DatePurchased >= '20070101' AND DatePurchased < '20100101'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 17:25:54

Wouldn't you need to GROUP BY YEAR also in case someone had 3 purchases in 2007 and none in 2008 or 2009?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-19 : 17:32:12

quote:
Originally posted by tkizer

Wouldn't you need to GROUP BY YEAR also in case someone had 3 purchases in 2007 and none in 2008 or 2009?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Ahh yeah, good point. updating my previous post.

drymnfr
Starting Member

9 Posts

Posted - 2010-04-19 : 18:20:10

Thank you very much, to both of you.

The query works as expected.

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-04-20 : 06:26:57

this works

KaShYaP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources