Geek Answers Handbook

How to include empty rows in a single GROUP BY DAY SQL query (date_field)?

I use MS SQL Server, but welcome comparative solutions from other databases.

This is the main form of my request. It returns the number of calls per day from the table "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

This data is used to draw a histogram, but if there were no calls on that day of the week, there is no result line and, therefore, no bar, and the user looks like this: "why does the graph only have six days and skip from Saturday to Monday?"

Somehow I need to CONNECT EVERYTHING with an empty string from every day or something like that, but I can't figure it out.

I am limited to what I can do with a single SQL statement, and I have read-only access, so I cannot create a temporary table or anything else.

+5
4

- ?

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..

+7

, , RIGHT JOIN ? ,

DECLARE @dateTable TABLE ([date] SMALLDATETIME)

INSERT INTO @dateTable
VALUES('26 FEB 2009')
INSERT INTO @dateTable
VALUES('27 FEB 2009')
-- etc

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
   RIGHT JOIN @dateTable dates
   ON incidentsm1.open_day = dates.date
   WHERE 
 DATEDIFF(DAY, open_time-(9.0/24), GETDATE())< 7

  ) inc1
GROUP BY open_day

, , ,

0

. , .

0

? - :

SELECT COUNT(*) AS Calls, ...
    FROM incidentsm1 RIGHT OUTER JOIN
         (SELECT date_values
            FROM TABLE(('27 Feb 2009'), ('28 Feb 2009'), ('1 Mar 2009'),
                       ('2 Mar 2009'), ('3 Mar 2009'), ('4 Mar 2009'),
                       ('5 Mar 2009')) AS date_list
         )
         ON ...

It is inspired by a kind of hybrid of Informix and DB2 notes and is pretty much guaranteed to be syntactically incorrect in both. Basically, is there a way in your DBMS to create a literal table on the fly. One possibility - ugly but barely doable - is to make 7-position UNION date literals selected from a "double" or some table expression that guarantees a single row (in Informix terms SELECT MDY(2,28,2009) FROM "informix".systables WHERE tabid = 1 UNION ...).

0
source

More articles:


All Articles