Oracle SQL - Comparing Multiple Rows with Dates
I'm working in Oracle SQL. Suppose I have a table that lists the following.
TABLE
PurchaseID CustID Location Date
1 1 A 8/23/2013 12:00:00 AM
2 1 B 8/15/2013 12:00:00 AM
3 2 A 5/15/2013 12:00:00 AM
4 2 B 1/01/2005 12:00:00 AM
5 3 A 1/15/2001 12:00:00 AM
6 3 A 1/30/2001 12:00:00 AM
7 3 B 8/23/2013 12:00:00 AM
8 4 A 5/05/2012 12:00:00 AM
9 4 B 8/15/2010 12:00:00 AM
10 4 A 9/20/2008 12:00:00 AM
I'm trying to write a query that compares the purchases by customer so
that the output is every instance where a particular customer makes a
purchase at two different locations within 2 years of each other. I'm
getting particularly tripped up on the CustID=3 and CustID=4 type cases,
where there are difficult combinations of location/date. The output of the
query should look like this.
PurchaseID CustID Location Date
1 1 A 8/23/2013 12:00:00 AM
2 1 B 8/15/2013 12:00:00 AM
8 4 A 5/05/2012 12:00:00 AM
9 4 B 8/15/2010 12:00:00 AM
10 4 A 9/20/2008 12:00:00 AM
In the Output, CustID=1's purchases are returned because they are in
different locations within 2 years of each other. CustID=2 are thrown out
because they are not within 2 years. CustID=3 has two purchases within 2
years of each other, but they are thrown out because they are in the same
location. And CustID=4's purchases are kept because Purchases 8 and 9 are
within 2 years and in different locations, and 9 and 10 are within 2 years
and in different locations (I want these to be kept despite 8 and 10 being
in the same location and not within 10 years).
Note: The Date column has the Oracle SQL 'Date' Datatype.
As always, any help/guidance would be greatly appreciated.
No comments:
Post a Comment