SQL problem

Tools
Enthusiast

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_MONTH
100 111 1
100 111 2
100 111 3
100 111 4
100 111 5
100 333 6
100 222 12
123 222 1
123 222 2
123 222 4
123 444 4
123 444 5
123 444 6

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.

Thanks & Regards,
Amit

5 REPLIES
Senior Apprentice

Re: SQL problem

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_n
from tab
where ...
group by cust_n
having count(distinct tr_month) = 6

But as the number of transactions is probably high, it's better to replace the distinct:

select
cust_n
from
(select cust_n, tr_month
from tab
where ...
group by 1,2
) t
group by cust_n
having count(*) = 6

Dieter
Enthusiast

Re: SQL problem

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

Senior Apprentice

Re: SQL problem

Hi Amit,
it's totally clear, that's why i wrote that solution using distinct.

Why didn't you simply try it?

Dieter
Enthusiast

Re: SQL problem

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
Enthusiast

Re: SQL problem

Try this

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.

Vinay