views:

1529

answers:

4

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.

A: 

Can you create a table variable with the dates that you need and then RIGHT JOIN onto it? For example,

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

The more ideal situation however, would be to have a table object with the dates in

Russ Cam
I can only use one SQL statement, so it looks like I can prepend the `DECLARE` but I can't insert the data in my SELECT statement.
Nathan
A: 

I would suggest the usage of a date table. With an existing date table in place, you can perform a RIGHT OUTER JOIN to the date table to bring in your missing days.

boflynn
sorry, I only have read-only access. I may resort to joining to a date column I'm pretty sure will always have a value for each day, but it seems like there should be a better way.
Nathan
A: 

Can you create the set of dates as part of your query? Something along the lines of:

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

This is inspired by a sort of hybrid of Informix and DB2 notations and is pretty much guaranteed to be syntactically incorrect in both. Basically, is there a way in your DBMS of creating a literal table on the fly. One possibility - ugly but barely doable - would be to do a 7-way UNION of date literals selected from 'dual' or some table expression that guarantees one row (in Informix terms, SELECT MDY(2,28,2009) FROM "informix".systables WHERE tabid = 1 UNION ...).

Jonathan Leffler
There's no DUAL in MSSQL, though I may still have luck using `WITH` (http://technet.microsoft.com/en-us/library/ms175972(SQL.90).aspx) ... Also, I need this query to run regularly, so I can't hardcode the dates but have to calculate them somehow, probably like `DATEADD(DAY, -1, GETDATE())`
Nathan
Fair enough; in Informix, that would be TODAY - 1, etc...
Jonathan Leffler
Note, there's no need for DUAL in mssql, since you don't need a from clause - as far as my understanding of DUAL goes :)
Blorgbeard
+4  A: 

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

Blorgbeard
THANK YOU! I was hoping for something more loopish but I can stomach 7 lines to make a virtual date table.N.B., I had to add an `ON` clause to the join to get it to work: ) inc1 ON days.open_day = incidents.open_day GROUP BY days.open_day BTW, Is NZ where the wild things are?
Nathan
oops thanks, fixed. Yes, we have vicious.. tuataras.. and great big.. kiwis.. yes.
Blorgbeard
You are my hero.
Nathan