Terradata Code error

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Visitor

Terradata Code error

Hi,

I have a dataset something like below. I'm trying to convert below SAS code to SQL( Teradata DB).

 

Work IdWork Status TmstpWork Status
10000000003/6/2016 21:28Suspended
10000000003/6/2016 21:28Resumed
10000000463/7/2016 13:41Suspended
10000000463/7/2016 13:41Resumed
10000001653/6/2016 23:15Suspended
10000001653/6/2016 23:15Resumed
10000002053/6/2016 23:10Suspended
10000002053/6/2016 23:11Resumed
10000002063/6/2016 23:10Suspended
10000002063/6/2016 23:10Resumed
10000002073/6/2016 23:10Suspended
10000002073/6/2016 23:11Resumed

 

SAS CODE :

format saveprevstatus $100.;
 retain saveprevstatus;
 format savetmstp datetime26.6;
 retain savetmstp;

 prevstatus = saveprevstatus;
 format suspendtmstp datetime26.6;
 suspendtmstp = savetmstp;
 format suspendtime 8.;

 if work_status = 'Suspended' then do;
  saveprevstatus = work_status;
  savetmstp = work_status_tmstp;
 end;
 if prevstatus = 'Suspended' and work_status = 'Resumed' then do;
  paired = 1;
  suspendtime = work_status_tmstp - suspendtmstp;
  saveprevstatus = .;
  savetmstp = .;
 end;

SAS Code O/P :

 

Work_IdWork_Status_TmstpWork_Statussaveprevstatussavetmstpprevstatussuspendtmstpsuspendtimepaired
100000000006Mar2016 21:28:44SuspendedSuspended06Mar2016 21:28:44.000    
100000000006Mar2016 21:28:56Resumed                                                                                                   . Suspended06Mar2016 21:28:44.000121
100000004607Mar2016 13:41:47SuspendedSuspended07Mar2016 13:41:47.000                                                                                                   .   
100000004607Mar2016 13:41:51Resumed                                                                                                   . Suspended07Mar2016 13:41:47.00041
100000016506Mar2016 23:15:20SuspendedSuspended06Mar2016 23:15:20.000                                                                                                   .   
100000016506Mar2016 23:15:28Resumed                                                                                                   . Suspended06Mar2016 23:15:20.00081
100000020506Mar2016 23:10:49SuspendedSuspended06Mar2016 23:10:49.000                                                                                                   .   
100000020506Mar2016 23:11:16Resumed                                                                                                   . Suspended06Mar2016 23:10:49.000271
100000020606Mar2016 23:10:29SuspendedSuspended06Mar2016 23:10:29.000                                                                                                   .   
100000020606Mar2016 23:10:31Resumed                                                                                                   . Suspended06Mar2016 23:10:29.00021
100000020706Mar2016 23:10:48SuspendedSuspended06Mar2016 23:10:48.000                                                                                                   .   

 

I have re-written the above sas query in Teradata  and tried to fetch same SAS results like below.. But its not working as expected. Can some one help me on this and educate me if my approach is wrong.

SQL CODE:

select a.*,b.work_status as saveprevstatus,b.work_status_tmstp as savetmstp,
case when b.work_status = 'Suspended' and a.work_status = 'Resumed' then a.work_status_tmstp - b.work_status_tmstp end as suspendtime,
case when b.work_status = 'Suspended' and a.work_status = 'Resumed' then 1 end as paried
from (select d.*, rank () over(partition by work_id order by   work_status) from egs_work_suspends_1 d ) a
left join (select c.*, rank () over(partition by work_id order by  work_status)from egs_work_suspends_1 c ) b
on a.work_id=b.work_id
and a.rank=b.rank+1 

 

Thanks,
Rajesh

1 REPLY
Highlighted
Teradata Employee

Re: Terradata Code error

Seems like you could do this without the self-join, but the main issue is ordering by Work_Status - which results in Resumed having Rank=1 and Suspended Rank=2.

It seems more logical to order by Work_Status_Tmstp.