title: SQL Add for Weekly & Monthly Totals
body: <p>I am trying to follow a similar example of getting weekly and monthly totals from:
  <a href="https://stackoverflow.com/questions/17102334/sql-add-sum-row-for-week-and-at-the-end-add-the-grand-total">StackOverFlow: SQL Add Sum Row for Week and At the End Add the Grand Total</a>.  This level of SQL is a stretch for me, so please be a clear as possible.</p>
  
  <p>I have a table with over 200K records of ID, Store_ID, Sales_Date, Amount.   </p>
  
  <pre><code>ID  Store_ID    Amount  Sales_Date
  1   215            7    1/29/2012
  2   215            7    1/30/2012
  3   215            7    1/31/2012
  4   215            7    2/1/2012
  5   215            7    2/2/2012
  6   215            7    2/3/2012
  7   215            7    2/4/2012
  8   215            8    2/5/2012
  9   215            8    2/6/2012
  10  215            8    2/7/2012
      ***More and More Data***        
  162 218            4    10/30/2011
  163 218            4    10/31/2011
  164 218            4    11/1/2011
  165 218            4    11/2/2011
  166 218            4    11/3/2011
  167 218            4    11/4/2011
  168 218            4    11/5/2011
  169 218            8    11/6/2011
  170 218            8    11/7/2011
  171 218            8    11/8/2011
             ******LOTS MORE DATA*****
  </code></pre>
  
  <p>I need to generate a view that will show the Weekly and Monthly Totals for each Store_ID and the associated date of the total.  The issue that I am having is that the example is providing me with Week totals(with no dates associated), Month totals (with no dates associated) and the daily amounts( which is a side benefit).</p>
  
  <p>I need to know how I can add in an additional column to the results that will show me the week ending date and the month ending date.</p>
  
  <p>This is what I have so far (it is almost exactly like the example):</p>
  
  <pre><code>set datefirst 7
  
  select top 100
      case
          when grouping(cast(datepart(week, [Sales_Date]) as varchar(255)))=1 then '&lt;MonthEnd&gt;'
          when grouping(cast([Sales_Date] as date))=1 then '&lt;weektotal&gt;'
          else cast(cast([Sales_Date] as date) as varchar(255))
      end as Period
      , WkSales = sum(Amount)
      , Store = Store_ID
  From KF_Store_Sales_Daily
  
  group by 
      grouping sets(  
      (cast(datepart(month, [Sales_Date]) as varchar(255)), cast(datepart(week, [Sales_Date]) as varchar(255)),cast([Sales_Date] as date)),
      (cast(datepart(month, [Sales_Date]) as varchar(255)), cast(datepart(week, [Sales_Date]) as varchar(255))),
      (cast(datepart(month, [Sales_Date]) as varchar(255)))
      )
      , Store_ID
  ORDER BY Store_ID, Sales_Date    
  </code></pre>