Average number of days to closure measure

[Dan Munns]

Hi all, 

Is there a way I can create a measure showing the average number of days between a call type being logged and being resolved? 

So Oct will only show stats for calls logged in Oct no matter when they were closed. 

Thanks 

[Dan Munns]

So I have come up with this but all it shows is '0' for all measures.

Help me @Victor, you're my only hope! 

(probably untrue but I haven't used a Star Wars reference in over a week and its killing me) 

[Dan Munns]

Ok so on further investigation it seems that none of the tickets I am looking at have a value in h_fixtime.

Neither do they have a value in h_catalog (weirdly) 

Quite a lot of the tickets I looked at (various types/services/catalog items) have no value in h_fixtime

I really need this report by tomorrow so any help would be greatly received. 

 

@Victor @Steven Boardman @Bob Dickinson

Edited February 26, 2018 by Dan Munns
Desperation

[Victor]

27 minutes ago, Dan Munns said:

none of the tickets I am looking at have a value in h_fixtime

@Dan Munns well then, you can't use that field... h_fixtime gets populated if the request had a resolve/response timer/target if the request didn't have these...well... no response and fix timers  

This also means you won't be able to use a measure for this purpose... unless you want to ignore such requests and put h_fixtime > 0 in where clause

If you can't ignore them then you need a widget or a report where you get UNIX_TIMESTAMP(h_dateclosed) - UNIX_TIMESTAMP(h_datelogged) as a result... unfortunately, even using the FORCE (  ), you can't have this in the measure as you have to select a field... 

[Dan Munns]

@Victor can I do a custom sql query widget to work out the average between h_datelogged and h_dateresolved? 

I got as far as SELECT * FROM h_itsm_requests WHERE h_catalog_id='95' AND h_status='status.closed' OR h_status='status.resolved' but can seem to get datediff to work

I was using it as avg(datediff(dd, h_datelogged, h_dateresolved)) so assumed that SELECT h_pk_requestid avg(datediff(dd, h_datelogged, h_dateresolved)) as avg_days FROM h_itsm_requests WHERE h_catalog_id='95' AND (h_status='status.closed' OR h_status='status.resolved') GROUP BY h_datelogged would work but apparently by SQL skills (if you can call them that) are lacking. 

[Dan Munns]

@Victor Ok so SELECT datediff (h_dateclosed, h_datelogged) FROM h_itsm_requests WHERE h_catalog_id='95' AND (h_status='status.closed' OR h_status='status.resolved') works in db direct and outputs a list of numbers. 

How can I (can I?) use this to work out the average in a widget or similar? 

[Victor]

@Dan Munns

6 minutes ago, Dan Munns said:

can I do a custom sql query widget to work out the average between h_datelogged and h_dateresolved?

Yes, this is what I was suggesting...

However, the DATEDIFF function you're trying to use in that statement is using the MS SQL syntax... you need to use a mySQL date/time function... mySQL also has a DATEDIFF function but that is returning the difference in days between two dates... if the dates are the same (e.g. request logged and closed same day) the function will return 0. So, you would need to use the one I suggested, it will look like:

SELECT AVG(UNIX_TIMESTAMP(h_dateclosed) - UNIX_TIMESTAMP(h_datelogged)) as avg_sec 
FROM h_itsm_requests WHERE h_catalog_id='95' AND (h_status='status.closed' OR h_status='status.resolved')
GROUP BY CONCAT(DAY(h_datelogged),'/',MONTH(h_datelogged),'/',YEAR(h_datelogged))

1. UNTIX_TIMESTAMP function converts the date/time value into seconds allowing to get values > 0 from the difference;
2. The result will be a number of seconds so you need to convert this in days/hours if needed;
3. Since h_datelogged is a date and time value (to the second), having it groped by simply this field will return/group every individual record, so you need to extract the date only form this field (done with the CONCAT function...)

[Victor]

2 minutes ago, Dan Munns said:

How can I (can I?) use this to work out the average in a widget or similar?

@Dan Munns by having a widget with a custom SQL query...

[Dan Munns]

@Victor thanks for the help so far. 

Using the query you posted it works if I remove the third line (although in seconds as you said) 

Adding the CONCAT line causes an error..

"Error

Error in stored query, see log for more details"

The log says:

MySQL Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GROUP BY CONCAT(DAY(h_datelogged),'/',MONTH(h_datelogged),'/',YEAR(h_datelogged)' at line 1 I will have a look tomorrow morning (in a panic) and see if I can work it out. Thanks again for pointing me in the right direction (and your seemingly infinite patience).

[Victor]

@Dan Munns you're missing an ending bracket there...

[Dan Munns]

@Victor I get The maximum number of rows allowed (100) has been exceeded, query aborted 

[Dan Munns]

Ok so adding a date filter of (DATE_FORMAT(h_datelogged, '%Y') >= 2018) I just get a long list of seconds so I don't think the CONCAT is working (or isn't working as I thought it would as I just expected a single number)

As for the error above, running the query in db direct pulled 122 results. Sadface.

The query I have so far is:

SELECT AVG(UNIX_TIMESTAMP(h_dateclosed) - UNIX_TIMESTAMP(h_datelogged)) as avg_sec 
FROM h_itsm_requests WHERE h_catalog_id='95' AND (h_status='status.closed' OR h_status='status.resolved') AND (DATE_FORMAT(h_datelogged, '%Y') >= 2018)
GROUP BY CONCAT(DAY(h_datelogged),'/',MONTH(h_datelogged),'/',YEAR(h_datelogged)) 

[Dan Munns]

Ok my bad. It is working as expected. I just missed a critical line:

5 hours ago, Victor said:

The result will be a number of seconds so you need to convert this in days/hours if needed;

Oh well, time to dig into the MariaDB knowledge base I guess

 

[Victor]

13 hours ago, Dan Munns said:

(DATE_FORMAT(h_datelogged, '%Y') >= 2018)

why so complicated?  stop thinking MS SQL  ... just use YEAR(h_datelogged) >= 2018

13 hours ago, Dan Munns said:

I just get a long list of seconds so I don't think the CONCAT is working (or isn't working as I thought it would as I just expected a single number)

The CONCAT function there will group records based on the day the request was logged. If you have different grouping criteria then this needs to be changed. For example, if you like to group and avg per month then grouping clause become simply GROUP BY MONTH(h_datelogged)

 

To convert the seconds into hours use this: ROUND(AVG(UNIX_TIMESTAMP(h_dateclosed) - UNIX_TIMESTAMP(h_datelogged))/3600, 2) as avg_hrs - gives you a number of hours with 2 decimals

To convert the seconds into days use this: ROUND(AVG(UNIX_TIMESTAMP(h_dateclosed) - UNIX_TIMESTAMP(h_datelogged))/86400, 2) as avg_day - gives you a number of days with 2 decimals

 

[Dan Munns]

@Victor MSSQL is not my strong point at the best of times but as I have approx 10 guys here who do it all day every day, stick with what they know huh? 

That being said, I owe you a beer (or 10) as I finally got it working. 

 

[Victor]

@Dan Munns