I'm new, and not even sure how to ask this question, but here goes:
For each customer/product purchase, I need to determine with SQL whether the same customer purchased that product in the 12 months prior to this purchase. So, if purchase is on 9/1/2018, I need to know if the customer purchased the same product in previous 12 months.
I've tried combinations of RANK() OVER(PARTITION BY but haven't really accomplished what I need to.
Edit: adding attempted code below:
select s.CUST_ACCT_ID ,s.PRODUCT ,s.BUS_MO ,s2.BUS_MO from INS_REF001_SALES_SUM_R48 s left outer join INS_REF001_SALES_SUM_R48 s2 on s.CUST_ACCT_ID = s2.CUST_ACCT_ID and s.PRODUCT = s2.PRODUCT and s.BUS_MO < s2.BUS_MO and s.BUS_MO >= dateadd(mm, datediff(mm, 0, s2.BUS_MO) - 12, 0) inner join INS_REF006_ACCT a on a.CUST_ACCT_ID = s.CUST_ACCT_ID and a.CUST_ACCT_ID = s2.CUST_ACCT_ID where s.BUS_MO between '11/01/2016' and '10/31/2018'