Time calculation not working as expected

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Time calculation not working as expected

Author

Topic

MarkPimblett
Starting Member

2 Posts

Posted - 2012-11-12 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-12 : 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 - 2012-11-12 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-12 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources