title: CROSS APPLY date table onto some data - why the duplication
body: <p>This is the data:</p>
  
  <pre><code>SELECT X.*
  INTO   #X
  FROM   (
         VALUES
         ('A',CAST('01 Feb 2013' AS DATETIME)),
         ('B','01 Mar 2013'),
         ('C','01 Sep 2013')
         ) X(Player,Mth)
  </code></pre>
  
  <p>We have a pretty standard factory <code>DimDate</code> table in our warehouse. </p>
  
  <p>If I do the following to find the number of days it works fine:</p>
  
  <pre><code>SELECT Player,Mth,
      numDays_mth = COUNT(XXX.DateKey),
  FROM #X A
      CROSS APPLY 
      (
      SELECT DateKey 
      FROM WHData.dbo.vw_DimDate DT 
      WHERE DT.DayMarker &gt;= A.Mth 
          AND DT.DayMarker &lt; DATEADD(MONTH, 1, A.Mth)
      ) XXX
  </code></pre>
  
  <p>Also for the number of days from the previous month things work fine:</p>
  
  <pre><code>SELECT Player,Mth,
      numDays_prevMth = COUNT(YYY.DateKey)     
  FROM #X A
      CROSS APPLY 
      (
      SELECT DateKey 
      FROM WHData.dbo.vw_DimDate DTT 
      WHERE DTT.DayMarker &gt;= DATEADD(MONTH, -1, A.Mth) 
      AND DTT.DayMarker &lt; A.Mth
      ) YYY
  </code></pre>
  
  <p>If I combine the two then there are problems:</p>
  
  <pre><code>SELECT Player,Mth,
      numDays_mth = COUNT(XXX.DateKey),
      numDays_prevMth = COUNT(YYY.DateKey)    
  FROM #X A
       CROSS APPLY 
       (
       SELECT DateKey 
       FROM WHData.dbo.vw_DimDate DT 
       WHERE DT.DayMarker &gt;= A.Mth 
           AND DT.DayMarker &lt; DATEADD(MONTH, 1, A.Mth)
       ) XXX
      CROSS APPLY 
      (
      SELECT DateKey 
      FROM WHData.dbo.vw_DimDate DTT 
      WHERE DTT.DayMarker &gt;= DATEADD(MONTH, -1, A.Mth) 
      AND DTT.DayMarker &lt; A.Mth
      ) YYY
  </code></pre>
  
  <p><strong>I realise there are plenty of other ways of getting these day counts</strong> but I'm trying to understand the workings of the <code>APPLY</code> operator as much as anything. </p>
  
  <p>How do I keep using <code>APPLY</code> twice and stop the duplication of data?</p>
  
  <hr>
  
  <p><strong>note</strong></p>
  
  <p>Changing the <code>SELECT</code> clause to the following works on this trivial example but has a massive impact on performance for a production script:</p>
  
  <pre><code>SELECT Player,Mth,
      numDays_mth = COUNT(DISTINCT XXX.DateKey),
      numDays_prevMth = COUNT(DISTINCT YYY.DateKey)   
  </code></pre>