Can someone solve it

Analytics
Highlighted
Enthusiast

Can someone solve it

So basically for each account type for a particular customer i want  a start and end date.

The account type can be repeated but then i still need a different start and end date for that account type for a particular customer


Accepted Solutions
Teradata Employee

Re: Urgent Help....

There may be a cleaner or more efficent way, but here's a solution:

 

Our final results may have more than one row for each customer / account pair if the service have ever lapsed. First, we used ordered analytics functions to find, for every customer / account pair, rows where the current date is not exactly one month from the previous or next row. This gives us dates of start or end of enrollment. Then, for each start, we look for the next end and do some math to coun the number of rows. 

 

I have tested this on your sample data and it produces the desired results (except you have typo on the last row of sample input - 2016 instead of 2006). 

 

SELECT
	CUSTOMER_ID
	,ACCT_TYPE
	,DT AS START_MONTH
	--LOOK AT ALL THE FOLLOWING ROWS, FIND THE MIN ROW THAT IS A SESSION END
	,MIN(CASE WHEN SESSION_END = 1 THEN DT END) OVER(
		PARTITION BY CUSTOMER_ID,ACCT_TYPE
		ORDER BY DT
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
		)	AS END_MONTH
		
	--SUBTRACT THE DATES AND ADD ONE TO GET ACOUNT OF MONTHS
	,((END_MONTH - START_MONTH) MONTH) 
		+ INTERVAL '1' MONTH AS NUMB_MONTH

FROM (
	SELECT 
		CUSTOMER_ID
		,ACCT_TYPE
		, DT
		--FOR EACH ROW, CHECK IF IT IS ONE MONTH AFTER THE PREVIOUS 
		--THIS WILL FIND ALL STARTING MONTHS
		,CASE WHEN MIN(DT) OVER(
				PARTITION BY CUSTOMER_ID,ACCT_TYPE 
				ORDER BY DT
				ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING --COMPAIR TO PREV ROW
			) + INTERVAL '1' MONTH = DT
			THEN 0 ELSE 1 END AS SESSION_START	
		--FOR EACH ROW, CHECK IF IT IS ONE MONTH AFTER THE NEXT ROW
		--THIS WILL FIND ALL ENDING MONTHS
		,CASE WHEN MIN(DT) OVER(
				PARTITION BY CUSTOMER_ID,ACCT_TYPE 
				ORDER BY DT
				ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING --COMPAIR TO NEXT ROW
			) - INTERVAL '1' MONTH = DT
			THEN 0 ELSE 1 END AS SESSION_END
	FROM SAMP
	--ONLY NEED TO RETURN ROWS THAT ARE EITHER START OR ENDS OF EACH SESSION
	QUALIFY (SESSION_START = 1 OR SESSION_END = 1)
) AS X
--ONLY KEEP ROWS WHERE THE SESSION STARTED
QUALIFY SESSION_START = 1
ORDER BY 1,3,2
1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: Urgent Help....

Why not simply:
SELECT customer_id, acct_type,
min(the_date) as start_date,
max(the_date) as end_date,
end_date - start_date  MONTH(4) as Total_Month,
Group By customer_id, acct_type

Teradata Employee

Re: Urgent Help....

There may be a cleaner or more efficent way, but here's a solution:

 

Our final results may have more than one row for each customer / account pair if the service have ever lapsed. First, we used ordered analytics functions to find, for every customer / account pair, rows where the current date is not exactly one month from the previous or next row. This gives us dates of start or end of enrollment. Then, for each start, we look for the next end and do some math to coun the number of rows. 

 

I have tested this on your sample data and it produces the desired results (except you have typo on the last row of sample input - 2016 instead of 2006). 

 

SELECT
	CUSTOMER_ID
	,ACCT_TYPE
	,DT AS START_MONTH
	--LOOK AT ALL THE FOLLOWING ROWS, FIND THE MIN ROW THAT IS A SESSION END
	,MIN(CASE WHEN SESSION_END = 1 THEN DT END) OVER(
		PARTITION BY CUSTOMER_ID,ACCT_TYPE
		ORDER BY DT
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
		)	AS END_MONTH
		
	--SUBTRACT THE DATES AND ADD ONE TO GET ACOUNT OF MONTHS
	,((END_MONTH - START_MONTH) MONTH) 
		+ INTERVAL '1' MONTH AS NUMB_MONTH

FROM (
	SELECT 
		CUSTOMER_ID
		,ACCT_TYPE
		, DT
		--FOR EACH ROW, CHECK IF IT IS ONE MONTH AFTER THE PREVIOUS 
		--THIS WILL FIND ALL STARTING MONTHS
		,CASE WHEN MIN(DT) OVER(
				PARTITION BY CUSTOMER_ID,ACCT_TYPE 
				ORDER BY DT
				ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING --COMPAIR TO PREV ROW
			) + INTERVAL '1' MONTH = DT
			THEN 0 ELSE 1 END AS SESSION_START	
		--FOR EACH ROW, CHECK IF IT IS ONE MONTH AFTER THE NEXT ROW
		--THIS WILL FIND ALL ENDING MONTHS
		,CASE WHEN MIN(DT) OVER(
				PARTITION BY CUSTOMER_ID,ACCT_TYPE 
				ORDER BY DT
				ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING --COMPAIR TO NEXT ROW
			) - INTERVAL '1' MONTH = DT
			THEN 0 ELSE 1 END AS SESSION_END
	FROM SAMP
	--ONLY NEED TO RETURN ROWS THAT ARE EITHER START OR ENDS OF EACH SESSION
	QUALIFY (SESSION_START = 1 OR SESSION_END = 1)
) AS X
--ONLY KEEP ROWS WHERE THE SESSION STARTED
QUALIFY SESSION_START = 1
ORDER BY 1,3,2
Enthusiast

Re: Urgent Help....

Thanks very much for your help,

I have tried to run your solution but getting the following error...

 

SELECT Failed. [7453] Interval field overflow.

 

Would you be able to suggest anything else...

 

Thanks

Enthusiast

Re: Urgent Help....

Thanks very much for you help MT186048    

All Sorted :)