0

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  ??
Debasis Das
  • 37
  • 5
  • Sorry, but your output is not readable. What are the datatypes of those columns? `cast(SLA_TMLINE as timestamp)` will return a timestamp bvased on current date. And then it's just some CASE... – dnoeth Aug 22 '20 at 15:41
  • Hi Dieter, Thanks for your reply. I have attached one picture to the question and below are the datatypes. SLA_TMLINE VARCHAR(25), SCH_STRT_TIME VARCHAR(25), START_TIME TIMESTAMP(0) – Debasis Das Aug 22 '20 at 15:53
  • Hi Dieter, When i am casting as time(0) it is giving us Invalid time error: SEL cast(lpad(nullif(SLA_TMLINE, 'NA'), 8, '0') as time(0) format 'hh:miBT') as SLA_TS FROM NDW_PRCS_CNTRL_VIEWS.NDW_JOB_DETAIL where REF_ID IN (4171,977,997,1428,755,898,145); Invalid Time error SEL lpad(nullif(SLA_TMLINE, 'NA'), 8, '0') AS COL_1,length(COL_1) AS COL_2 FROM NDW_PRCS_CNTRL_VIEWS.NDW_JOB_DETAIL where REF_ID IN (4171,977,997,1428,755,898,145); COL_1 COL_2 06:00 AM 8 02:30 AM 8 23:04 PM 8 06:00 AM 8 ? ? ? ? ? ? – Debasis Das Aug 23 '20 at 08:03

1 Answers1

1

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
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Hi Dieter, When i am casting as time(0) it is giving us Invalid time error: SEL cast(lpad(nullif(SLA_TMLINE, 'NA'), 8, '0') as time(0) format 'hh:miBT') as SLA_TS FROM NDW_PRCS_CNTRL_VIEWS.NDW_JOB_DETAIL where REF_ID IN (4171,977,997,1428,755,898,145); Invalid Time error SEL lpad(nullif(SLA_TMLINE, 'NA'), 8, '0') AS COL_1,length(COL_1) AS COL_2 FROM NDW_PRCS_CNTRL_VIEWS.NDW_JOB_DETAIL where REF_ID IN (4171,977,997,1428,755,898,145); COL_1 COL_2 06:00 AM 8 02:30 AM 8 23:04 PM 8 06:00 AM 8 ? ? ? ? ? ? – Debasis Das Aug 23 '20 at 08:19
  • Well, `23:04 PM` is not a well-formed AM/PM time, check if it's actually 24 hours and the AM/PM is just infomational and cab be skipped – dnoeth Aug 23 '20 at 08:34
  • Thanks, got it. Apart from that it is working for other dates. – Debasis Das Aug 23 '20 at 09:02
  • 1
    If AM/PM is not needed you can simply change the formnat to `'hh:mi'` – dnoeth Aug 23 '20 at 09:17