title: CTE over id, status, and date causing results to square
body: <p>I have a table defined as</p>
  
  <pre><code>CREATE TABLE ItemDetail (
      ItemNumber        bigint not null,
      SiteId            int not null,
      Status            int not null,
      ScanDate          datetime not null,
  )
  </code></pre>
  
  <p>What I am trying to do is get counts by site, by status, by day.  I have a CTE as defined</p>
  
  <pre><code>;WITH statCTE AS (
      SELECT
          Count(ItemNumber) over(partition by SiteId, Status, DATEADD(dd, 0, DATEDIFF(dd, 0, ScanDate))) as ItemCount,
          SiteId,
          Status,
          DATEADD(dd, 0, DATEDIFF(dd, 0, ScanDate)) AS ScanDate
      FROM
          ItemDetail
  )
  </code></pre>
  
  <p>The problem is that when I then run </p>
  
  <pre><code>select * from statCTE where siteid = 119 and scandate = '3/3/2011'
  </code></pre>
  
  <p>I get</p>
  
  <pre><code>ItemCount SiteId Status ScanDate
  2   119 0   2011-03-03 00:00:00.000
  2   119 0   2011-03-03 00:00:00.000
  6   119 5   2011-03-03 00:00:00.000
  6   119 5   2011-03-03 00:00:00.000
  6   119 5   2011-03-03 00:00:00.000
  6   119 5   2011-03-03 00:00:00.000
  6   119 5   2011-03-03 00:00:00.000
  6   119 5   2011-03-03 00:00:00.000
  </code></pre>
  
  <p>The result set should be 2 rows, one with 2 for status 0 and one with 6 for status 6.  So, my partition isn't working with transforming the date into just a date object and picking the results.   I could just square root the <code>ItemCount</code> totals in my final query (a pivot), but that's more of a hack than fixing the actual problem.</p>