October 3, 2008 at 3:52 pm
I am stuck on a query that doesn't seam to have a proper set based approach.
The query is suppose to find customers who have placed orders within 1 week of each other and return the first order and any following order as a pair.
[font="Courier New"]
Cust|Order|Date
--------------------------------
10001|A1230001|Jan 1st 2008
10002|A1230002|Jan 2nd 2008
10002|A1230003|Jan 3rd 2008
10001|A1230004|Jan 4th 2008
10002|A1230005|Jan 5th 2008
10005|A1230006|Jan 6th 2008
10006|A1230007|Jan 7th 2008
10001|A1230008|Jan 8th 2008
[/font]
In this dataset I would want to return:
Base, Duplicate
--------------------
A1230001, A1230004
A1230004, A1230008
A1230002, A1230003
A1230002, A1230005
Notice that A1230001 and A123004 is within one week and so gets returned as a pair but a123001 and A123008 are not within one week are not returned as a pair. Since A123004 and A1230008 are within a week they get returned as a pair. :crazy:
A1230002 is returned as a pair to A1230003 and to A1230005.
Alas, this query is driving me insane. It doesn't seem possible to accomplish.
I already have to build the data into temporary tables so I am beginning to lean toward some sort of while loop but I am hoping one of you geniuses has a better solution, or at least a good kick in the right direction.
Thanks
Travis
October 3, 2008 at 4:08 pm
This should get you started.
select
a.[Cust],
Order1= a.[Order],
Order1Date= a.[Date],
Order2= b.[Order],
Order1Date= b.[Date]
from
Orders a
join
Orders b
on
a.[Cust]= b.[Cust]and
a.[Order]<> b.[Order]and
a.[Date]<= b.[Date]and
a.[Date]> dateadd(dd,-7,b.[Date])
order by
a.[Cust],
a.[Order],
b.[Order],
b.[Date]
October 3, 2008 at 4:38 pm
Well that looks like an excellent start, and much simpler then the approach I was taking.
I guess I was just stuck on using datediff...
Much Appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply