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

Question

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!

Answer 1

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.