select customer purchase less than 10 days apart

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

SQL Server 2008 Forums

Transact-SQL (2008)

select customer purchase less than 10 days apart

Author

Topic

spinningtop
Starting Member

29 Posts

Posted - 2012-07-06 : 07:34:33

Hi
I have a table with the columns 'StoreID', 'CustomerID' and 'DateOfPurchase'. I just want to select the StoreID and the CustomerID for each customer at the same store where their date of purchase is less than 10 days apart.

So from the table below I would select storeID 0001 and Customer A and both dates of purchase as they are less than 10 days apart.

StoreId CustomerID DateofPurchase
0001 A 01/01/2001
0001 A 02/01/2001
0001 B 01/01/2003
0002 A 01/01/2001
0002 A 20/01/2001
0002 B 01/01/2003

Should fairly simple but I can't figure it out. Should create a derived table with join on StoreID and CustomerID ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-06 : 07:40:51

One way to do this is as follows:

SELECT
	a.StoreId,
	a.CustomerId,
	DATEDIFF( dd,b.DateofPurchase, a.DateofPurchase) AS Interval
FROM
	YourTable a
	CROSS APPLY
	(
		SELECT TOP (1) b.DateOfPurchase
		FROM YourTable b
		WHERE b.DateofPurchase < a.DateofPurchase
		AND a.CustomerId = b.CustomerId
		ORDER BY b.DateofPurchase DESC 
	) b
WHERE
	DATEDIFF( dd,b.DateofPurchase, a.DateofPurchase) < 10

spinningtop
Starting Member

29 Posts

Posted - 2012-07-06 : 07:52:44

This is great, works perfectly, thank you sunita.

I have never come across CROSS APPLY before but I will read up on it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-06 : 08:16:37

You are welcome!

CROSS APPLY and OUTER APPLY are sort of similar to INNER JOINS and LEFT JOINS, but different in that, logically the left table expression is evaluated first and then the right - which allows you to correlate the right expression with the left.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources