Database

## Help in Ordered Analytic Functions

Code:

`select subscr_id, eff_from_dt ,eff_to_dt ,typfrom ( select subscr_id ,typ ,cod1       ,min(eff_from_dt) as eff_from_dt       ,max(eff_to_dt) as eff_to_dt    from    ( sel subscr_id ,typ ,eff_from_dt ,eff_to_dt          ,case when cod = 0                then max(cod) over (partition by subscr_id,typ                                    order by eff_from_dt                                    rows unbounded preceding)               else cod           end as cod1      from        ( SELECT Subscr_ID, typ, Eff_From_Dt, Eff_To_Dt,              Rank() OVER (ORDER BY subscr_id,eff_from_dt ) as rk,              CASE WHEN COALESCE(MAX(typ) OVER (PARTITION BY Subscr_ID                                               ORDER BY Eff_From_Dt                                                ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) <> typ                   THEN rk                   ELSE 0              END AS Cod FROM tableA        )a     )b    group by 1,2,3 )c`

Dear Experts,

Please help in having the optimal code. The attached picture has the rows of table A and the desired output. I have given in the code snippet which the code i have written to get the output.Not sure whether there is any other easy way to write this query. Please have a look and suggest the best method to do this . Thanks in advance for your valuable time.

3 REPLIES
Supporter

## Re: Help in Ordered Analytic Functions

On which version of TD are you?

Check if you can use the TD_NORMALIZE_... functions

they should do what you want.

Source for information: SQL Functions, Operators, Expressions, and Predicates

## Re: Help in Ordered Analytic Functions

Thanks for the response Ulrich .The version is TD 14.10 and i am hearing the first time about the TD_NORMALIZE functions :-). The functions are compatible in 14.10 and will try out them and update here.

## Re: Help in Ordered Analytic Functions

Rewrote

`sel subscr_id,typ,eff_from_dt,eff_to_dt from(sel subscr_id, typ,min(eff_from_dt) over (partition by subscr_id,typ order by eff_from_dtreset when COALESCE(MAX(Typ) over (partition by subscr_id order by eff_from_dt rows 1 preceding), '99999999999') <> typ) as eff_from_dt,max(eff_to_dt) over (partition by subscr_id,typ order by eff_from_dtreset when COALESCE(MAX(Typ) over (partition by subscr_id order by eff_from_dt rows 1 preceding) , '99999999999')<> typ) as eff_to_dtfrom preced1 ) a group by 1,2,3,4`

using reset clause. still have to explore the normalize functions.