Maximum Tickets served per hour in each service

This is an answering service that has tickets from several different services and I want to receive hourly tickets serviced by my employees at each service

I have the following code and data base ouput: Print of the code

SELECT gfqueuecounter_counter,hora,gfqueue_desc  FROM (SELECT gfqueuecounter_time,gfqueue_desc, gfqueuecounter_counter , SUBSTRING(CONVERT(gfqueuecounter_time,TIME),1,2) as hora
FROM gfqueuecounter_v2 
WHERE gfqueuecounter_time > '2022-06-07 09:00:00' and gfqueuecounter_time < '2022-06-07 18:00:00') as t1;

The red lines are one example of the lines that i want from the table but just from one 9 o'clock

The Column "gfqueuecounter_counter" is the counter of each ticket in each service but it counts one by one so i want to get the last count of every hour for that specific service

The column "gfqueue_desc" is the different types of tickets

The column "hora" displays every hour

The second "select" it is just for me to split the "gfqueuecounter_time" in to hour

Any question i will be happy to answer

CodePudding user response：

you should try this, it maybe works

SELECT gfqueue_desc, COUNT(gfqueuecounter_counter), SUBSTRING(CONVERT(gfqueuecounter_time,TIME),1,2) as hora FROM gfqueuecounter_v2
WHERE gfqueuecounter_time > '2022-06-08 09:00:00' and gfqueuecounter_time < '2022-06-08 18:00:00' GROUP BY gfqueue_desc,hora ORDER by hora