title: Pulling a month of rows in SQL Server based on a dynamic day
body: <p>I have a table in SQL server that looks like this (there are other fields, but there are the only 2 I care about):</p>
  
  <pre><code>-----------------------------------------
  |    DraftDay (int)|  MonthlyFee (money)|
  -----------------------------------------
        1                 18.75
        2                 15.25
        2                 15.25
        3                 15.25
        3                 28.12
        4                 15.25
        4                  3.75
        4                 18.19
        5                 12.75
        6                 13.80
        6                 14.25
       ...                 ...
       ...                 ...
  </code></pre>
  
  <p>What I am trying to do is write a query that will group by <code>DraftDay</code> and sum the <code>MonthlyFee</code> field. Simple enough. But the hard part is, I need to get the date of one month ago, plus one day and pull the days from that day to the current day. For example, if today is 4/3/2014, then I need to pull all days between 3/4/2014 and 4/3/2014. My result set should look like this:</p>
  
  <pre><code>3/4/2014         37.19
  3/5/2014         12.75
  3/6/2014         28.05
   ...              ...
   ...              ...
  4/1/2014         18.75
  4/2/2014         30.50
  4/3/2014         43.37
  </code></pre>
  
  <p>I have created this query:</p>
  
  <pre><code>SELECT 
      CAST(CAST(DATEPART(MONTH, DATEADD(DAY, 1, DATEADD(MONTH, -1, GETDATE()))) AS VARCHAR(2)) + '/' +
      CAST(CASE WHEN EftDraftDay&gt;28 THEN 28 ELSE EftDraftDay END AS VARCHAR(2)) + '/' +
      CAST(DATEPART(YEAR, DATEADD(DAY, 1, DATEADD(MONTH, -1, GETDATE()))) AS VARCHAR(4)) AS DATETIME) AS DraftDate,
      SUM(MonthlyPayment) AS MonthlyPayment FROM dbo.Advertiser
  WHERE IsDeleted=0
      AND IsPaidInFull=0
      AND IsAdvertiserActive=1
      AND EftDraftDay&gt;0
      AND (CAST(CAST(DATEPART(MONTH, DATEADD(DAY, 1, DATEADD(MONTH, -1, GETDATE()))) AS VARCHAR(2)) + '/' +
      CAST(CASE WHEN EftDraftDay&gt;28 THEN 28 ELSE EftDraftDay END AS VARCHAR(2)) + '/' +
      CAST(DATEPART(YEAR, DATEADD(DAY, 1, DATEADD(MONTH, -1, GETDATE()))) AS VARCHAR(4)) AS DATETIME)) &gt;=
      CAST(CAST(DATEPART(MONTH, DATEADD(DAY, 1, DATEADD(MONTH, -1, GETDATE()))) AS VARCHAR(2)) + '/' +
      CAST(DATEPART(DAY, DATEADD(DAY, 1, DATEADD(MONTH, -1, GETDATE()))) AS VARCHAR(2)) + '/' +
      CAST(DATEPART(YEAR, DATEADD(DAY, 1, DATEADD(MONTH, -1, GETDATE()))) AS VARCHAR(4)) AS DATETIME)
  GROUP BY 
      CAST(CAST(DATEPART(MONTH, DATEADD(DAY, 1, DATEADD(MONTH, -1, GETDATE()))) AS VARCHAR(2)) + '/' +
      CAST(CASE WHEN EftDraftDay&gt;28 THEN 28 ELSE EftDraftDay END AS VARCHAR(2)) + '/' +
      CAST(DATEPART(YEAR, DATEADD(DAY, 1, DATEADD(MONTH, -1, GETDATE()))) AS VARCHAR(4)) AS DATETIME)
  </code></pre>
  
  <p>Because today's date is 4/17/2014, I should be getting results back from 3/18/2014 thru 4/17/2014. The results I'm getting back are only from 3/18/2014 thru 3/28/2014. How can I also add 4/1/2014 thru 4/17/2014 into my result set? I thought about a <code>UNION</code>, but those don't seem to work well with <code>GROUP BY</code>'s. </p>
  
  <p>After trying to figure this out and looking at my query, I think I am totally over engineering this. My query looks so complex for such a simple thing. Can anyone please help me out with this?</p>