Jump to content
James Bartle

Problem with custom SQL query

Recommended Posts

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?

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

@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

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


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

Share this post


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

  • Like 1
  • Thanks 1

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