CODEWITHSUNDEEP
sqlt-sql
BornInChicago
BornInChicago

Reputation: 169

How find Customers who Bought Product A and D > 6 months apart?

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

Answers (4)

Carl Manaster
Carl Manaster

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

RichardTheKiwi
RichardTheKiwi

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

  • A in Jan,
  • A in March,
  • A in July, and then purchased
  • D in September

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

Paul Kearney - pk
Paul Kearney - pk

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

Andrew Skirrow
Andrew Skirrow

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

Related Questions