All Forums Tools
Amit 8 posts Joined 02/06
23 Jun 2006
SQL problem

Hi All,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.Sample data:CUST_N ACCNT TR_MONTH100 111 1100 111 2100 111 3100 111 4100 111 5100 333 6100 222 12123 222 1123 222 2123 222 4123 444 4123 444 5123 444 6With 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.Thanks & Regards,Amit

dnoeth 4628 posts Joined 11/04
23 Jun 2006

Hi Amit,it's easy if you know how to write a WHERE-condition to filter for data from the last 6 months:select cust_nfrom tabwhere ...group by cust_nhaving count(distinct tr_month) = 6But as the number of transactions is probably high, it's better to replace the distinct:select cust_nfrom (select cust_n, tr_month from tab where ... group by 1,2 ) tgroup by cust_nhaving count(*) = 6Dieter

Dieter

Amit 8 posts Joined 02/06
26 Jun 2006

Thanks Dieter for your response.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.Thanks & Regards,Amit

dnoeth 4628 posts Joined 11/04
26 Jun 2006

Hi Amit,it's totally clear, that's why i wrote that solution using distinct.Why didn't you simply try it?Dieter

Dieter

Amit 8 posts Joined 02/06
26 Jun 2006

Thanks Dieter,I apologise for overlookin Distinct clause. Actually I was not aware of using Distinct in Having clause. I never used my common sense that I should try using Distinct while counting.Thanks a lot for your prompt response.With warm regards,Amit

BBR2 96 posts Joined 12/04
26 Jun 2006

Try thisSEL CUST_N ,TR_MONTH ,SUM(MOVING_DIFF) OVER (PARTITION BY CUST_N ORDER BY TR_MONTH) AS GRP_SUMFROM (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_CUSTQUALIFY (GRP_SUM= 5);This should give you your required results of consecutive transactions.Vinay

You must sign in to leave a comment.