All Forums Database
Umamageshwaran 4 posts Joined 11/14
20 Nov 2015
Help in Ordered Analytic Functions

Code:

select subscr_id, eff_from_dt ,eff_to_dt ,typ
from
 ( 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.
 

ulrich 816 posts Joined 09/09
20 Nov 2015

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 
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Umamageshwaran 4 posts Joined 11/14
20 Nov 2015

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.

Umamageshwaran 4 posts Joined 11/14
21 Nov 2015

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_dt
reset 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_dt
reset when COALESCE(MAX(Typ) over 
(partition by subscr_id order by eff_from_dt rows 1 preceding) , '99999999999')<> typ) as eff_to_dt
from preced1 ) a group by 1,2,3,4

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

You must sign in to leave a comment.