Diff between two timestamps expressed as an SLA

[QFanatic]

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.

[ManuelRühl · Accepted solution]

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

[micheledenardi]

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
];

[QFanatic]

Thank you!