title: SQL Server - can't do COUNT on DATEDIFF without doing CREATE VIEW
body: <p>I am trying to get some information on the differences in two dates and how often that difference has occurred. I can use:</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>And it will return:</p>
  
  <pre><code>Days
  -----
  4
  4
  8
  21
  16
  42
  ...
  </code></pre>
  
  <p>Now I want to get a count of those days, something like:</p>
  
  <pre><code>Days    Diff
  -----   -----
  0       100
  1       40
  2       22
  3       25
  ...
  </code></pre>
  
  <p>I don't have permissions in the db to CREATE VIEW so I was thinking a nested statement like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But I keep getting error Msgs 207 and 208!</p>
  
  <p>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:</p>
  
  <pre><code>SELECT Days, COUNT (*) AS Diff
  FROM new_dual_db_alias
  WHERE EXISTS 
  </code></pre>
  
  <p>Thanks,</p>