SQL Server - can't do COUNT on DATEDIFF without doing CREATE VIEW

Question

Hi,

I am trying to get some information on the differences in two dates and how often that difference has occurred. I can use:

SELECT 
   DATEDIFF (day, db1.dbo.t1.Date1, db2.dbo.t2.Date2) AS Days
FROM 
   db1.dbo.t1 
JOIN 
   db2.dbo.t2 ON db1.dbo.t1.wID = db2.dbo.t2.cID
              AND db1.dbo.t1.Action LIKE 'Standard'
              AND db2.dbo.t2.Status = 'CLOSED'

And it will return:

Days
-----
4
4
8
21
16
42
...

Now I want to get a count of those days, something like:

Days    Diff
-----   -----
0       100
1       40
2       22
3       25
...

I don't have permissions in the db to CREATE VIEW so I was thinking a nested statement like:

SELECT 
    Days, COUNT (*) AS Diff
FROM 
    Days
WHERE EXISTS 
(
SELECT DATEDIFF (day, db1.dbo.t1.Date1, db2.dbo.t2.Date2) AS Days
FROM db1.dbo.t1 JOIN db2.dbo.t2
ON db1.dbo.t1.wID = db2.dbo.t2.cID
AND db1.dbo.t1.Action LIKE 'Standard'
AND db2.dbo.t2.Status = 'CLOSED'
)
GROUP BY Days

But I keep getting error Msgs 207 and 208!

Please tell me what I am doing wrong. I have been searching and trying various things but so far all I can think of is somehow I need to give an alias to the 'db1.dbo.t1 JOIN db2.dbo.t2' so I can call it with:

SELECT Days, COUNT (*) AS Diff
FROM new_dual_db_alias
WHERE EXISTS

Thanks,

Answer 1

I think - though I'm not in a position to test quickly:

SELECT Days, COUNT(Days) FROM
(
SELECT 
    DATEDIFF (day, db1.dbo.t1.Date1, db2.dbo.t2.Date2) AS Days
FROM
    db1.dbo.t1 JOIN db2.dbo.t2 ON db1.dbo.t1.wID = db2.dbo.t2.cID AND 
                                  db1.dbo.t1.Action LIKE 'Standard' AND 
                                  db2.dbo.t2.Status = 'CLOSED'
)
GROUP BY Days

Answer 2

On SQL Server 2005 and up, you could also use a CTE (Common Table Express)

WITH DateDiffDays AS
(
  SELECT 
     DATEDIFF (day, db1.dbo.t1.Date1, db2.dbo.t2.Date2) AS Days
  FROM 
     db1.dbo.t1 
  JOIN 
     db2.dbo.t2 ON db1.dbo.t1.wID = db2.dbo.t2.cID
  WHERE
     db1.dbo.t1.Action LIKE 'Standard'
     AND db2.dbo.t2.Status = 'CLOSED'
)
SELECT Days, COUNT(*)
FROM DateDiffDays
GROUP BY Days
ORDER BY COUNT(*)

A CTE is something like a "view on the fly" which is valid for just the next statement.

Does that work for you?