I'm using MS SQL Server but welcome comparitive solutions from other databases.
This is the basic form of my query. It returns the number of calls per day from the 'incidentsm1' table:
SELECT
COUNT(*) AS "Calls",
MAX(open_time),
open_day
FROM
(
SELECT
incident_id,
opened_by,
open_time - (9.0/24) AS open_time,
DATEPART(dd, (open_time-(9.0/24))) AS open_day
FROM incidentsm1
WHERE
DATEDIFF(DAY, open_time-(9.0/24), GETDATE())< 7
) inc1
GROUP BY open_day
This data is used to draw a bar graph, but if there were no calls on a given day of the week, there is no result row and thus no bar, and the user is like, "why does the graph only have six days and skip from Saturday to Monday?"
Somehow I need to UNION ALL with a blank row from each day or something like that, but I can't figure it out.
I am constrained to what I can do with one SQL statement and I have readonly access so I can't create a temporary table or anything.
How about something like this?
SELECT
COUNT(incident_id) AS "Calls",
MAX(open_time),
days.open_day
FROM
(
select datepart(dd,dateadd(day,-6,getdate())) as open_day union
select datepart(dd,dateadd(day,-5,getdate())) as open_day union
select datepart(dd,dateadd(day,-4,getdate())) as open_day union
select datepart(dd,dateadd(day,-3,getdate())) as open_day union
select datepart(dd,dateadd(day,-2,getdate())) as open_day union
select datepart(dd,dateadd(day,-1,getdate())) as open_day union
select datepart(dd,dateadd(day, 0,getdate())) as open_day
) days
left join
(
SELECT
incident_id,
opened_by,
open_time - (9.0/24) AS open_time,
DATEPART(dd, (open_time-(9.0/24))) AS open_day
FROM incidentsm1
WHERE DATEDIFF(DAY, open_time-(9.0/24), GETDATE()) < 7
) inc1 ON days.open_day = incidents.open_day
GROUP BY days.open_day
I've only tested it on a simplified table schema, but I think it should work. You might need to tinker with the dateadd stuff..
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With