|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2 days ago @ 3:30:42 AM
Posts: 8,
Visits: 3
|
|
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
Teradata Certified Professional
Amit
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 3:41:05 AM
Posts: 522,
Visits: 264
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2 days ago @ 3:30:42 AM
Posts: 8,
Visits: 3
|
|
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
Teradata Certified Professional
Amit
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 3:41:05 AM
Posts: 522,
Visits: 264
|
|
Hi Amit,
it's totally clear, that's why i wrote that solution using distinct.
Why didn't you simply try it?
Dieter
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2 days ago @ 3:30:42 AM
Posts: 8,
Visits: 3
|
|
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
Teradata Certified Professional
Amit
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/7/2008 6:20:30 PM
Posts: 136,
Visits: 11
|
|
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
|
|
|
|