Archives of the TeradataForum

Message Posted: Tue, 29 Mar 2011 @ 20:50:30 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I have just about got this thing wrapped up. I have been able to get previous, current and next times and limit with particular code. Now i need to figure out a way to get TOPIC before and after current topic per Client ID.

Here is the code that i have so far .. bonus points to anyone that can help! Im new to creating reports especially in Teradata so if someone has any resources for finding sample code for putting records in sequential value i would greatly appreciate it.

     select
     client_id,business_category,Topic_Code,PreviousDate,Activity_Timestamp,NextDate,
     (Activity_Timestamp - PreviousDate day(4) to second) as tsdiff,
        (extract(day from tsdiff) * 86400)
        + (extract(hour from tsdiff) * 3600)
        + (extract(minute from tsdiff) * 60)
        + extract(second from tsdiff) as sec_diff
      from (
     select
     client_id,business_category,Topic_Code,
     min(cast(Activity_Timestamp as timestamp)) over (partition by Client_Id order by
     Activity_Timestamp asc rows between 1 PRECEDING  and 1 following)  as
     PreviousDate,
     cast(Activity_Timestamp as timestamp) as Activity_Timestamp,
     min(cast(Activity_Timestamp as timestamp)) over (partition by Client_Id order by
     Activity_Timestamp asc rows between 1 following  and 1 following)  as NextDate
     from or_10_093
     Qualify NextDate is not null
     where business_category = 'billing'
     group by client_id,Business_Category,
     Topic_Code,Activity_Timestamp)
      billing_valid
      where sec_diff between 600 and 1209600.000000
      order by Client_Id,Activity_Timestamp

Thank you in advance!

	<Prev	Next>		<<First	<Prev	Next>	Last>>