Problem with custom SQL query

[James Bartle]

Hello,

We previously had a widget setup that would show each users record number of calls resolved within a working week ever.

It simply showed their name and their record number of calls resolved.

However, we have not used this for a while and when I went to look at it today it flagged up an error stating "The maximum number of rows allowed (100) has been exceeded, query aborted".

The query is as follows:

SELECT
    h_resolvedby_user_id,
    COUNT(h_pk_reference) as resolved_count,
    CONCAT(YEAR(h_datelogged), '/', WEEK(h_datelogged)) as year_week
FROM
    h_itsm_requests
WHERE
    h_resolvedby_user_id IS NOT NULL
GROUP BY
    h_resolvedby_user_id, year_week
ORDER BY
    resolved_count DESC

 

Can anyone help?

Is there a better way to do this now?

[Victor]

36 minutes ago, James Bartle said:

Is there a better way to do this now?

We can start by looking why you need to display "ever" data in a widget... Can this be limited to last or a year or something like this?

[James Bartle]

On 8/8/2018 at 4:49 PM, Victor said:

We can start by looking why you need to display "ever" data in a widget... Can this be limited to last or a year or something like this?

Hi Victor, Thanks for getting back to me.

What we would prefer is to be able to have the all of the data since we started using hornbill to be included this as the idea is that it displays the all time records for our administrators.

[Victor]

@James Bartle - I am not questioning your processes it just seems a bit odd to me to have this info displayed on a widget. I would see such information displayed in a report perhaps. In any case, the number of records are limited in widgets, as  you can see it only display 100. You reached this limit once the number of weeks you display there reached 100 (which is roughly 2 years)...

[James Bartle]

@Victor - Is that a new change because it has worked fine up until now?

 

Also, what would be the best way to display this info in a report?

 

Thank you

[Victor]

6 minutes ago, James Bartle said:

Is that a new change because it has worked fine up until now?

There was no change, number of records/results displayed in widget was always limited to 100... the reason why it didn't happen until now is because your widget is grouping results by week. So, one result/'record or line for each week... It worked fine while the number of weeks displayed was less than 100. However this number only grows, and it will continue to grow, as you display all data, from all time. So you displayed more and more weeks until the number of weeks displayed was greater than 100 ... 

[James Bartle]

That's alright @Victor, Thanls for the explanation.

As far as the other part of this goes, what would be the best way to set up the report?

Much appreciated

[Steve Giller]

On 8/8/2018 at 4:12 PM, James Bartle said:

We previously had a widget setup that would show each users record number of calls resolved within a working week ever.

The Query you detail returns the total number of calls per user, per week. If you are after each user's record surely you'd need a MAX() in the Query so that it is returning one row (highest count and year/week) per Analyst?

No, I don't know how to do this.

[James Bartle]

15 minutes ago, DeadMeatGF said:

The Query you detail returns the total number of calls per user, per week. If you are after each user's record surely you'd need a MAX() in the Query so that it is returning one row (highest count and year/week) per Analyst?

No, I don't know how to do this.

Hi @DeadMeatGF, Where would I need to put that in the query exactly?

[Steve Giller]

1 hour ago, DeadMeatGF said:

No, I don't know how to do this.

Caveat above ^^^

However:

SELECT h_resolvedby_user_id, year_week, MAX(resolved_count) AS record FROM (
	SELECT
	    h_resolvedby_user_id,
	    COUNT(h_pk_reference) as resolved_count,
	    CONCAT(YEAR(h_datelogged), '/', WEEK(h_datelogged)) as year_week
	FROM
	    h_itsm_requests
	WHERE
	    h_resolvedby_user_id IS NOT NULL
	GROUP BY
	    h_resolvedby_user_id, year_week
	ORDER BY
	    resolved_count DESC ) AS x
GROUP BY x.h_resolvedby_user_id
ORDER BY record DESC

appears to work.

Of course, if you have more than 100 Analysts you will hit the same error, and I have only tried this in Database Direct, so there may be additional factors in a Measure.

[James Bartle]

Thank you very much @DeadMeatGF, That's worked quite well