Find status change of account over a period of time

Database
Fan

Find status change of account over a period of time

Hi All,

 

I need to find status change of a account and the range of it.The same status can come any day continuously and already appeared status can come again.

 

Source:

Account number    status     effective date

123                         y              01-01-2018

123                         y              02-01-2018

123                         y              05-01-2018

456                         y              06-01-2018

123                         N              07-01-2018

456                         y               08-01-2018

123                         y               09-01-2018

 

 

Expected Result:

Account number           status                   start date                           end date

123                                 y                         01-01-2018                        07-01-2018

123                                 N                         07-01-2018                        09-01-2018

123                                 y                         09-01-2018                        31-12-9999

456                                 y                         06-01-2018                         31-12-9999

 

Thanks  in advance.


Accepted Solutions
Fan

Re: Find status change of account over a period of time

Thanks Fred for the help will try and let you know.

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: Find status change of account over a period of time

One option:

Convert each source row to one with an effective period using an OLAP function and apply NORMALIZE; then optionally switch back to two individual DATE columns instead of a PERIOD.

SELECT AccountNumber, Status, BEGIN(Effective_Period) as StartDate, END(Effective_Period) as EndDate
FROM
 ( 
	SELECT NORMALIZE AccountNumber, Status, 
	PERIOD(EffectiveDate, 
		COALESCE( /* Use ordered analytic function to pick up effective date from next row as period end */
			MIN(EffectiveDate) OVER (PARTITION BY AccountNumber ORDER BY EffectiveDate ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
			,date'9999-12-31' /* Or if this is last row for the AccountNumber, use high date as period end */
			)
		) as Effective_Period
	FROM SourceTable
 ) as NormalizedRows
Fan

Re: Find status change of account over a period of time

Thanks Fred for the help will try and let you know.

Fan

Re: Find status change of account over a period of time

Thanks fred for the help.It worked .. :)