-1

I love this site, and it's helped me tons but this is my first posted question. I searched and cannot find an answer to this specific question. Very new to SQL.

Let's say I have a table that breaks down each line in an invoice where 

TableName
OrderNumber, OrderDate, Product, CustomerID

What I want is to find out what customerID have ordered the SAME product more than once where those SAME product orders occurred within 90 days of each other.

I successfully made a query where I found customers that ordered the same product more than once:

select customerid, product, count(distinct ordernumber) as "count of orders" 
from TableName
where orderdate > '2017-01-01'
group by customerid, product
having count(ordernumber) > 1

I can't seem to think my way through the rest.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
Kyngfish
  • 1
  • 2

1 Answers1

-1
select distinct a.customerid, a.product
from TableName a
join TableName b
  on a.orderdate > '2017-01-01'
 and b.orderdate >= a.orderdate
 and datediff(dd, a.orderdate, b.orderdate) <= 90 
 and a.customerid   = b.customerid
 and a.product      = b.product
 and a.ordernumber <> b.ordernumber
paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • Downvoted as, while it works, it doesn't really do anything to explain why it works to a newer sql user, as the OP is. – Marshall Tigerus Dec 12 '17 at 21:22
  • @MarshallTigerus Would you rather have an explanation and an answer that does not work? If they cannot figure out what it does from the syntax then documentation is readily available. – paparazzo Dec 12 '17 at 21:25
  • @paparazzi So - just to understand, you've created a virtual table with the same values and where the date for the second table occurs after the first table, and the difference in dates is less than 90 days, matching up the fields that should match. If I want to add the counts, I just add a count(a.customerid), and then it looks like i also need to group by a.customerid, a.product, is this correct? – Kyngfish Dec 12 '17 at 21:33
  • @Kyngfish Give it a try – paparazzo Dec 12 '17 at 22:33
  • @Paparazzi since SO is designed around actually understanding why things work and not just a 'fix my problem' site, I'd rather have an answer that does both. Your answer is not as straightforward as you might think to a SQL newbie. – Marshall Tigerus Dec 14 '17 at 14:52