CODEWITHSUNDEEP
sqlteradatateradata-sql-assistantteradatasql
Debasis Das
Debasis Das

Reputation: 57

Teradata timestamp value adjustments

PictureCan someone please help me implementing below 2 requirements where i am currently stuck now:

  1. I need help in calculating & populating field 'DURATION_CROSSED_AFTER_SLA' in minutes only when STATUS of the job is 'IN PROGRESS' and it is greater than SLA_TMLINE. If SLA_TMLINE is 'NA' then field will be 'NA' as well. SLA_TMLINE value is given in time(EST) only, while calculating it will automatically assume current day, like for example: for JOB_ID 1373, SLA_TMLINE is given as - 06:00 AM, which means '8/22/2020 06:00:00'.
  2. Also if status of the job is 'IN PROGRESS', and if current timestamp(0) > SLA_TMLINE for current day + '1' hour then we need to show status(B.STATUS) of the job as 'LONG RUNNING', instead of 'IN PROGRESS'.

Basically we will be having below statuses for a job:

WAITING
IN PROGRESS
LONG RUNNING
COMPLETE
FAILED

From above 5 job statuses, 'LONG RUNNING' status is not available in table, but we will derive it based on point no. 2 above.

Query: SEL A.JOB_ID,A.SUBJECT_AREA,A.UC4JOB AS UC4_JOB,A."WORKFLOW/JOB" AS 
 INFA_WORKFLOW,B.STATUS,A.SCH_STRT_TIME,B.START_TIME,A.SLA_TMLINE,
 '??' AS DURATION_CROSSED_AFTER_SLA
 FROM NDW_PRCS_CNTRL_VIEWS.NDW_JOB_DETAIL A JOIN 
 NDW_PRCS_CNTRL_VIEWS.NDW_JOB_RUN_STATUS B 
 ON A.REF_ID = B.REF_ID 
 AND A.JOB_ID = B.JOB_ID 
 WHERE A.REF_ID IN (4171,977,997,1428,755,898,145) 
 AND B.DATA_DATE = DATE - 1;

            
Current Output we are getting:
JOB_ID  SUBJECT_AREA    UC4_JOB INFA_WORKFLOW   STATUS  SCH_STRT_TIME   START_TIME  SLA_TMLINE  DURATION_CROSSED_AFTER_SLA
1,373   Subs by Speed   NDW.EPC.SUBS_BY_SPEED_DATA_LOAD wf_EPC_SUBS_BY_SPEED    COMPLETE    6:00 AM 8/22/2020 06:01:24  6:00 AM ??
343 CSG Run 2   NDW.P1P2MIG.NDW_CCS_DATA_EXTRACT.WF_CSG_EXTRACT_EQP_MERGE_SA    wf_CSG_EXTRACT_EQP_MERGE_SA COMPLETE    23:00 PM    8/21/2020 23:00:24  23:04 PM    ??
906 Billing NDW.P2.IN.WF_ROSETTA_SEMANTIC_ACCOUNT_LEDGER_FACT   wf_ACCOUNT_LEDGER_FACT_INC  COMPLETE    04:00 AM    8/22/2020 02:39:35  06:00 AM    ??
539 iControl    NDW.XHBU.SPRINT1.INFA_WF_DEPENDENT.ICONTROL_STG_JRNL_LOAD   wf_XH_BU_ICONTROL_dly_inc   COMPLETE    05:00 AM    8/22/2020 05:01:25  NA  ??
668 XH BB/Cell Offline  NDW.XHBU.SPRINT2.INFA_WF_DEPENDENT.BB_CELL_OFFLINE_DLY  wf_XH_BB_CELL_OFFLINE_DLY   COMPLETE    06:00 AM    8/22/2020 07:19:49  NA  ??
2,042   NDW_XHBU    NDW_XH.JOBS.SPRINT2.INFA_WF_DEPENDENT.XH_EQUIPMENT_ORDER_DLY    Wf_XH_EQUIPEMENT_ORDER_ACT_DLY  WAITING NA  ?   NA  ??

Upvotes: 0

Views: 500

Answers (1)

dnoeth
dnoeth

Reputation: 60502

The tricky part is the time stored as VarChar and the single digit hours. Adding the missing leading zero can be done using a RegEx or LPAD:

cast(lpad(nullif(SLA_TMLINE, 'NA'), 8, '0') as time(0) format 'hh:miBT')

When you CAST this to a TimeStamp it defaults to today:

cast(cast(lpad(nullif(SLA_TMLINE, 'NA'), 8, '0') as time(0) format 'hh:miBT') as timestamp(0)) as SLA_TS

And then it's

case
  when status = 'IN PROGRESS' 
   and SLA_TS < current_timestamp - interval '1' hour
  then 'LONG RUNNING'
  else status
end

case
  when status = 'IN PROGRESS' 
   and SLA_TS < current_timestamp
  then (current_timestamp(0) - SLA_TS minute(4))
end

Upvotes: 1

Related Questions