Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have 2 timestamps ..a CloseTime...2021/08/25 11:35:24, and an OpenTime...2021/08/20 10:07:47.
I subtract these in script and I get a time difference of 51:04:44 which is correct by using interval(DTTM_REQUEST_CLOSE - DTTM_ASSIGNED_DATE,'hh') .
now I want to compare that difference to 24 hours - if it is more I want to flag SLA_Breached as 'Yes'.
I have tried every variation I can think of, but to no luck...
1. if(Timestamp#(DTTM_REQUEST_CLOSE) - Timestamp#(DTTM_ASSIGNED_DATE) > maketime(24), 'Y','N') as SLA_BREACHED
2. if(hour(interval(DTTM_REQUEST_CLOSE - DTTM_ASSIGNED_DATE)) > 24, 'Y','N')
None of them work. Any idea how I can do this comparison?
Thanks.
24h are one day. So you could just check, wether the difference is >1 :
if(Timestamp#(DTTM_REQUEST_CLOSE) - Timestamp#(DTTM_ASSIGNED_DATE) > 1, 'Y','N') as SLA_BREACHED
24h are one day. So you could just check, wether the difference is >1 :
if(Timestamp#(DTTM_REQUEST_CLOSE) - Timestamp#(DTTM_ASSIGNED_DATE) > 1, 'Y','N') as SLA_BREACHED
Try this:
Load *,
if(SubField(interval(DTTM_REQUEST_CLOSE - DTTM_ASSIGNED_DATE),':',1)>24,'Y','N') as SLA_BREACHED;
load
Timestamp(Timestamp#(DTTM_ASSIGNED_DATE,'YYYY/MM/DD hh:mm:ss')) as DTTM_ASSIGNED_DATE,
Timestamp(Timestamp#(DTTM_REQUEST_CLOSE,'YYYY/MM/DD hh:mm:ss')) as DTTM_REQUEST_CLOSE
inline [
DTTM_ASSIGNED_DATE,DTTM_REQUEST_CLOSE
2021/08/20 10:07:47,2021/08/25 11:35:24
];
Thank you!