We have a requirement wherein we need to gather the list of customers having transaction accounts in the last consecutive 6 months. A customer can have more than one account & irrespective of their accounts we need to list down the customer having consecutive last 6 months transactions.
With this kind of data we need to write a query to retrieve only those customers who have data in each of the last six months, like in this example it should be customer number '100'. Customer '123' should be ignored as it does not have data for last six months CONSECUTIVELY.
If I use this SQL then it will return me with all the customers having data for 6 months only. But this is not my requirement. I need customers having CONSECUTIVE 6 months data. If we consider period of Jan to June then customer should have data in each of the months like Jan-Feb-Mar-Apr-May-June. Data should be present for each month.
Your solution will retreive Data for even those customer who might have data twice in Jan thrice in Mar & once in June and have nothing in Feb, Apr & May.
I hope you understand my problem. If it is still not clear then just let me know.
SEL CUST_N ,TR_MONTH ,SUM(MOVING_DIFF) OVER (PARTITION BY CUST_N ORDER BY TR_MONTH) AS GRP_SUM
FROM (SEL CUST_N ,TR_MONTH ,TR_MONTH - SUM(TR_MONTH) OVER( PARTITION BY CUST_N ORDER BY TR_MONTH ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS MOVING_DIFF FROM CUST QUALIFY (MOVING_DIFF IS NULL OR MOVING_DIFF = 1) GROUP BY 1,2 ) AS DER_CUST
QUALIFY (GRP_SUM= 5);
This should give you your required results of consecutive transactions.