我正在使用MS SQL Server,但欢迎来自其他数据库的比较解决方案.
这是我查询的基本形式.它返回'incidentsm1'表中每天的调用次数:
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
此数据用于绘制条形图,但如果在一周中的某一天没有调用,则没有结果行,因此没有条形图,并且用户就像是,"为什么图表只有六天,从周六到周一跳过?"
不知怎的,我需要UNION ALL每天都有一个空行或类似的东西,但我无法弄明白.
我受限于我可以用一个SQL语句做什么,我只读访问所以我不能创建一个临时表或任何东西.
这样的事怎么样?
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
我只在一个简化的表模式上测试它,但我认为它应该工作.你可能需要修补dateadd的东西..