I have the requirements to look at a person, their eligibility dates and their products. Whenever the months in between are NOT continuous, or whenever the product changes, I need the row number to reset back to one.
, DENSE_RANK() OVER
( PARTITION BY PERSON_NO, PRODUCT
ORDER BY ELIG_DT
RESET WHEN PRODUCT <> AVG(PRODUCT)
AND ELIG_DT > MIN(ELIG_DT)
PARTITION BY PRODUCT
ORDER BY ELIG_DT DESC
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) )
WHERE PERSON_NO = '9—‘
AND PRODUCT LIKE ANY ('599','4%')
AND ELIG_DT BETWEEN '20150501' AND '20160401'
GROUP BY 1,2,3
ORDER BY 3 DESC;
What happens is:
Product a, which begin as of 5/1/2015, is good for five months (5/1, 6/1, 7/1, 8/1, and 9/1) and then it moves to product b, which starts on 10/1 and stops after 11/1. When product a resumes on 12/1/2016, the row number continues on with "6" when it should reset back to 1 because the months are not continuous or the product has changed.
Please advise the best way to do this.
I pasted the data in the previous posting. The issue is that the 12/1/2015 record should not have row number (column RN) showing 6; rather, it should re-start at 1 because either the product changed (from 425 to 599) or the ELIG_DT is not the next month following. In this instance, it should change only because of the product changing.
Let me know if I can provide more context, and thank you, in advance, for this help.