Finding the Next Order - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Finding the Next Order

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-18 : 09:07:27

Hi Guys

I have the following problem:
I have the following two tables. One shows when a customer has contacted us, and the second shows any orders that been made by the customer. (Both tables have been scaled down)

ContactTable (Scaled Down):

CusID,ContactDate
1,2009-01-01
1,2009-01-02
1,2009,01,05
2,2009-01-02
3,2009-01-03
3,2009-01-10
4,2009-01-02
4,2009-01-04
4,2009-01-05

OrdersTable (Scaled Down):

OrderID, CusID,OrderDate
1,1,2009-12-31
2,1,2009-01-02
3,1,2009-01-03
4,2,2009-01,20
5,3,2009-01-02
6,3,2009-01-11
7,4,2008-12-31
8,4,2009-01-09
9,4,2009-01-11
10,4,2009-04-05
11,4,2009-01-06

The thing that I am trying to achieve (but have been stuck on for quite some time now) is when a customer has contacted I want to be able to show the next order they have placed after that contact.

So the desired result would be something like below:
CusID,ContactDate,OrderID,OrderDate
1, 2009-01-01, 2, 2009-01-02
1, 2009-01-02, 3, 2009-01-03
1, 2009,01,05, NULL, NULL
2, 2009-01-02, 4, 200-01-20
3, 2009-01-03, 6, 2009-01-11
3, 2009-01-10, 6, 2009-01-11
4, 2009-01-02, 9, 2009-01-11
4, 2009-01-04, 9, 2009-04-05
4, 2009-01-05, 9, 2009-01-06

I’ve tried using the ROW_NUMBER and MIN functions but still had no luck.

Anyone have an ideas on how I can achieve this???

Thanking you in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-18 : 09:40:23

Yes. Visakh will shortly show you a method with CROSS APPLY.

E 12°55'05.63"
N 56°04'39.26"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-18 : 10:15:11

OK, Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-18 : 13:09:03

Apparently Visakh is slacking.. :)

Here is one method usng CROSS APPLY:

DECLARE @Contact TABLE (CusID INT ,ContactDate DATETIME)

INSERT @Contact
SELECT 1, '2009-01-01'
UNION ALL SELECT 1, '2009-01-02'
UNION ALL SELECT 1, '2009-01-05'
UNION ALL SELECT 2, '2009-01-02'
UNION ALL SELECT 3, '2009-01-03'
UNION ALL SELECT 3, '2009-01-10'
UNION ALL SELECT 4, '2009-01-02'
UNION ALL SELECT 4, '2009-01-04'
UNION ALL SELECT 4, '2009-01-05'

DECLARE @Orders TABLE (OrderID INT, CusID INT, OrderDate DATETIME)
INSERT @Orders
SELECT 1,1,'2009-12-31'
UNION ALL SELECT 2,1,'2009-01-02'
UNION ALL SELECT 3,1,'2009-01-03'
UNION ALL SELECT 4,2,'2009-01-20'
UNION ALL SELECT 5,3,'2009-01-02'
UNION ALL SELECT 6,3,'2009-01-11'
UNION ALL SELECT 7,4,'2008-12-31'
UNION ALL SELECT 8,4,'2009-01-09'
UNION ALL SELECT 9,4,'2009-01-11'
UNION ALL SELECT 10,4,'2009-04-05'
UNION ALL SELECT 11,4,'2009-01-06'

SELECT
	C.CusID,
	C.ContactDate,
	D.OrderID,
	D.OrderDate
FROM
	@Contact AS C
CROSS APPLY
	(
		SELECT TOP 1 OrderID, OrderDate
		FROM @Orders AS O
		WHERE O.CusID = C.CusID
		AND O.OrderDate > C.ContactDate 
		ORDER BY OrderDate ASC
	) AS D

NOTE: I think the data is slightly off for the first Order date??

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-19 : 06:38:16

Thats Great

Is there any way of showing the NULL values to, Ie. Displaying contacts even if an order hasn't been made after the contact date.

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-19 : 12:28:24

Yeah, if you change the CROSS APPLY to an OUTER APPLY that will get you the null results. However, your sample data does not produce a NULL. The first row in in the Order data is 12/31/09 so that shows up where you indicate you want a NULL value in your result set. Maybe that is just a typo on the date..?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-06-20 : 08:26:38

Hey

Thanks for that!

Yes it was a typo.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-20 : 11:51:02

Visakh pro in cross apply?hehe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources