Count of requests by date

[will.good]

Hi,

I am looking to create a report that would show a count of requests by source by date between a filter date range.

e.g. if the date range was 01/03/2023 - 31/03/2023 it would provide a count of each source type for 01/03, 02/03 and so on

I would be able to do this fairly easily if the date logged didn't include the time (as i could just group by date logged)

Any suggestions on the easiest way to do this?

[will.good]

I *think* I have been able to create a sql that does this... would appreciate if someone could confirm (SQL is not my most confident of areas)

select left(h_datelogged,10) as "Date Logged",
SUM(case
when h_source_type = "Analyst" THEN 1
END) AS "Analyst", 
SUM(case
when h_source_type = "Email" THEN 1
END) AS "Email", 
SUM(case
when h_source_type = "Chat" THEN 1
END) AS "Chat", 
SUM(case
when h_source_type = "Self Service" THEN 1
END) AS "Self Service", 
SUM(case
when h_source_type = "Autoresponder" THEN 1
END) AS "Autoresponder"
FROM h_itsm_requests
WHERE (h_datelogged >= '2023-04-03 00:00:00' and
h_datelogged <= '2023-04-09 23:59:59')
group by left(h_datelogged,10)