Maximum number of tickets served per hour per service - SQL

Question

This is an answering service that has tickets from several different services and I would like to receive tickets for each service's employees providing service by the hour

I have the following code and database output: Print 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 line is an example of the line I want to see from the table, but only starting at 9 o'clock

The column "gfqueuecounter_counter" is a counter for each ticket in each service, but it is counted one by one, so I want to get the last count per hour for that specific service

The "gfqueue_desc" column is a different type of ticket

The "hora" column is displayed every hour

The second "select" is just for me to split "gfqueuecounter_time" into hours

I will be happy to answer any questions

Answer 1

You should try this, it might work:

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