Reputation: 169
I need advice from more advanced SQL experts on this.
I am being asked to create a report showing customers who bought Product 105 and who then bought Product 312 more than 6 months later.
For example, I have the following Orders table:
RecID CustID ProdID InvoiceDate
1 20 105 01-01-2009
2 20 312 01-04-2009
3 20 300 04-20-2009
4 31 105 07-10-2005
5 45 105 10-03-2007
6 45 300 11-10-2007
7 45 312 08-25-2008
I need a report that looks at this table and comes back with:
CustID ElapsedDays
45 327
Do I need to use a cursor and iterate record by record, comparing dates as I go?
If so, what would the cursor procedure look like? I have not worked with cursors, although I have done years of procedural programming.
Thanks!
Upvotes: 5
Views: 3317
Reputation: 40356
You've got some good answers above; a self-join is the way to go. I want to suggest to you how best to think about a problem like this. What if you had the purchases of Product A and D in different tables? Not that you should store the data that way, but you should think about the data that way. If you did, you could join, say, product_a_purchases to product_d_purchases on customer ID and compare the dates. So, for purposes of your query, that's what you need to produce. Not an actual on-disk table that is product_a_purchases, but a table of records from your purchases table that includes only Product A purchases, and the same for Product D. That's where the self-join comes about.
Upvotes: 2
Reputation: 107826
select A.CustID, ElapsedDays = datediff(d, A.InvoiceDate, B.InvoiceDate)
from Orders A
inner join Orders B on B.CustID = A.CustID
and B.ProdID = 312
-- more than 6 months ago
and B.InvoiceDate > dateadd(m,6,A.InvoiceDate)
where A.ProdID = 105
The above query is a simple interpretation of your requirement, where ANY purchase of A(105) and D(312) occurred 6 months apart. If the customer purchased
it would return 2 rows for the customer (Jan and March), since both of those are followed by a D purchase more than 6 months later.
The following query instead finds all cases where the LAST A purchase is 6 months or more before the FIRST D purchase.
select A.CustID, ElapsedDays = datediff(d, A.InvoiceDate, B.InvoiceDate)
from (
select CustID, Max(InvoiceDate) InvoiceDate
from Orders
where ProdID = 105
group by CustID) A
inner join (
select CustID, Min(InvoiceDate) InvoiceDate
from Orders
where ProdID = 312
group by CustID) B on B.CustID = A.CustID
-- more than 6 months ago
and B.InvoiceDate > dateadd(m,6,A.InvoiceDate)
And if for the same scenario above, you don't want to see this customer because the A (Jul) and D (Sep) purchases are not 6 months apart, you can exclude them from the first query using an EXISTS
filter.
select A.CustID, ElapsedDays = datediff(d, A.InvoiceDate, B.InvoiceDate)
from Orders A
inner join Orders B on B.CustID = A.CustID
and B.ProdID = 312
-- more than 6 months ago
and B.InvoiceDate > dateadd(m,6,A.InvoiceDate)
where A.ProdID = 105
AND NOT EXISTS (
SELECT *
FROM Orders C
WHERE C.CustID=A.CustID
AND C.InvoiceDate > A.InvoiceDate
and C.InvoiceDate < B.InvoiceDate
and C.ProdID in (105,312))
Upvotes: 1
Reputation: 5543
You can do this with a self-join:
select a.custid, DATEDIFF(dd, a.invoicedate, b.invoicedate)
from #t a
inner join #t b
on a.custid = b.custid
and a.prodid = 105
and b.prodid = 312
where DATEDIFF(dd, a.invoicedate, b.invoicedate) > 180
The first use of #t (aliased a) is for the first product and the second use of #t (aliased b) is for the second product. Here's the script I used to test it:
create table #t (
recid int,
custid int,
prodid int,
invoicedate date)
insert into #t select 1, 20, 105, '1/1/2009'
insert into #t select 2, 20, 312,'1/4/2009'
insert into #t select 3, 20, 300,'4/20/2009'
insert into #t select 4, 31, 105,'7/10/2005'
insert into #t select 5, 45, 105,'10/3/2007'
insert into #t select 6, 45, 300,'11/10/2007'
insert into #t select 7, 45, 312,'8/25/2008'
select a.custid, DATEDIFF(dd, a.invoicedate, b.invoicedate)
from #t a
join #t b
on a.custid = b.custid
and a.prodid = 105
and b.prodid = 312
where DATEDIFF(dd, a.invoicedate, b.invoicedate) > 180
drop table #t
Upvotes: 1
Reputation: 3451
Probably something like this would work:
select CustID, datediff(day, O1.InvoiceDate, O2.InvoiceDate) as ElapsedDays
from Orders O1
inner join Orders O2
on O1.CustId = O2.CustId
and dateadd(month, 6, O1.InvoiceDate) <= O2.InvoiceDate
where
O1.ProdId = 105
and O2.ProdId = 312
Upvotes: 0