Jump to content
Dan Munns

Average number of days to closure measure

Recommended Posts

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 

Share this post


Link to post
Share on other sites

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) 

Capture.PNG

Share this post


Link to post
Share on other sites

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 by Dan Munns
Desperation

Share this post


Link to post
Share on other sites
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 ( :P ), you can't have this in the measure as you have to select a field... 

Share this post


Link to post
Share on other sites

@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. 

Share this post


Link to post
Share on other sites

@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? 

Share this post


Link to post
Share on other sites

@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...)

  • Like 1

Share this post


Link to post
Share on other sites
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...

Capture1.PNG

Share this post


Link to post
Share on other sites

@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). 

Share this post


Link to post
Share on other sites

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)) 

Share this post


Link to post
Share on other sites

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 :(

 

Share this post


Link to post
Share on other sites
13 hours ago, Dan Munns said:

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

why so complicated? :D stop thinking MS SQL :D ... 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

 

  • Like 1

Share this post


Link to post
Share on other sites

@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. 

 

Capture.PNG

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...