| Author |
 Topic  |
|
|
MarkPimblett
Starting Member
2 Posts |
 Posted - 11/12/2012 : 08:24:09
|
I have completed the below query to return process time for our goods in activity and also then used a case when <'24:00:00' for SLA HIT or SLA FAIL
problem i have is that the formula is returning incorrectly
1-2 hours SLA HIT
2-10 hours SLA FAIL
11-24 Hours SLA HIT
>24 hours SLA FAIL
i dont understand why > 2 <10 is SLA FAIL
select
/*it.site_id,
it.code,
it.from_loc_id,
it.to_loc_id,
it.sku_id,
it.tag_id,
to_char(it.dstamp, 'DD-MON-YYYY') "DATE",
it.update_qty*/
to_char(it.dstamp, 'DD-MON-YYYY') "DATE",
it.sku_id "SKU",
it.client_id "CLIENT",
it.tag_id "TAG",
it.update_qty "QTY",
to_char(it.dstamp, 'DD-MON-YYYY HH24:MI:SS') "RECEIPT",
to_char(it2.dstamp, 'DD-MON-YYYY HH24:MI:SS') "PUTAWAY",
LibDate.ConvertSecsToHMS
(LibDate.IntervalSecond
((CASE when it2.code = 'Putaway' then it2.dstamp else null end)
-
(CASE when it.code = 'Receipt' then it.dstamp else null end))) "PROCESS TIME",
case when LibDate.ConvertSecsToHMS (LibDate.IntervalSecond (MAX (it2.dstamp) - MAX (it.dstamp))) < '24:00:00' then 'SLA HIT' else 'SLA FAIL' end "SLA ACHIEVEMENT"
from Inventory_transaction it
full outer join inventory_transaction it2 on it.tag_id = it2.tag_id
where
it.site_id like 'TIC'
and
it.client_id in ('P82GB72','P82SL82')
and
it.code like 'Receipt'
and
it2.code like 'Putaway'
and
(trunc(it.dstamp,'DDD') >= '01-NOV-2012'
and
trunc(it.dstamp,'DDD') < '12-NOV-2012')
group by it.code, it2.code, it.dstamp, it2.dstamp, it.tag_id, it.update_qty, it.sku_id, it.client_id
order by it.dstamp asc
Example of returns
01-NOV-2012 GPO:11000111 P82SL82 89014 100 01-NOV-2012 11:41:40 01-NOV-2012 21:55:58 10:14:18 SLA HIT
1-NOV-2012 LEN:PWC704D637-OCT P82SL82 89015 80 01-NOV-2012 11:43:32 01-NOV-2012 15:04:00 3:20:28 SLA FAIL
01-NOV-2012 LEN:PWC704D637-OCT P82SL82 89016 80 01-NOV-2012 11:43:33 01-NOV-2012 15:11:21 3:27:48 SLA FAIL
01-NOV-2012 LEN:PWC704D637-OCT P82SL82 89017 80 01-NOV-2012 11:43:33 01-NOV-2012 15:21:26 3:37:53 SLA FAIL
01-NOV-2012 WPN-HP24TFT P82GB72 89018 1 01-NOV-2012 11:43:56 01-NOV-2012 13:03:12 1:19:16 SLA HIT
01-NOV-2012 LEN:PWC704D637-OCT P82SL82 89019 60 01-NOV-2012 11:44:01 01-NOV-2012 15:28:48 3:44:47 SLA FAIL
01-NOV-2012 RBN-HPUSBKEYBOARD P82GB72 89020 55 01-NOV-2012 11:44:20 01-NOV-2012 13:22:13 1:37:53 SLA HIT
01-NOV-2012 RBNB-DL38014014477 P82GB72 89021 4 01-NOV-2012 11:46:20 01-NOV-2012 19:09:55 7:23:36 SLA FAIL[/size=2][/size=1] |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/12/2012 : 08:35:26
|
Your case expression should be something like shown below. The way you have it now, it categorizes everything that is less than 24 hours into one single category.
Couple of things to note:
a) It would be more efficient to compare seconds rather than do a conversion to HMS. Per row function calls are usually expensive.
b) The BETWEEN clause I am using is inclusive, so for example, between 1:00:00 and 2:00:00 will include both 1:00:00 and 2:00:00 (i.e., a closed set).CASE
WHEN LibDate.ConvertSecsToHMS (LibDate.IntervalSecond (MAX(it2.dstamp) - MAX(it.dstamp)))
BETWEEN '1:00:00' AND '2:00:00'
OR
LibDate.ConvertSecsToHMS (LibDate.IntervalSecond (MAX(it2.dstamp) - MAX(it.dstamp)))
BETWEEN '11:00:00' AND '24:00:00' THEN 'SLA HIT'
ELSE 'SLA FAIL'
END "SLA ACHIEVEMENT"
|
 |
|
|
MarkPimblett
Starting Member
2 Posts |
Posted - 11/12/2012 : 09:13:23
|
my original post may have come across incorrectly
i do want < 24:00:00 SLA HIT
> 24:00:00 SLA FAIL
i have modified the statement to between 00:00:01 to 24:00:00 but still getting the same problem
anything >2 <10 is coming up as SLA fail |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/12/2012 : 11:12:34
|
What I posted earlier should have given "SLA HIT" for any duration >= 1:00:00 and <= 2:00:00 or duration >= 11:00:00 and <= 24:00:00 and "SLA FAIL" for everything else. If that is not what you require, change the numbers accordingly.
Are you using Microsoft SQL Server? If you are using Oracle, I don't know the behavior of the case expression, or even whether the syntax is supported the same way. This forum is for Microsoft SQL Server, so if you are on another DBMS, you would get better and faster responses at a forum such as dbforums.com |
|
|
| |
Topic |
|
|
|
Time calculation not working as expected
