title: How to get the date from last Sunday to Saturday
body: <p>I will entries in my table which will have date with data like this:</p>
  
  <pre><code>Date          Value
  4-20-2014     45
  4-21-2014     15
  4-22-2014     35
  ...
  4-30-2014     109
  </code></pre>
  
  <p>I will be executing my Stored Procedure every Sunday which means I would like to get the row with <code>Date</code> from previous Sunday until Yesterday (Saturday).</p>
  
  <p>I have the following query which will get from Previous Sunday to Previous Saturday which I think is wrong so I modified it as the following:</p>
  
  <pre><code>INSERT INTO [database].[dbo].[table]
  SELECT (WEEKENDING DATE) AS [Date], SUM([ORDERS]) AS Orders, SUM([B ORDERS]) AS bOrders, SUM([RESULT]) AS Results
  FROM [database].[dbo].[origtable]
  WHERE
      [Date] &gt;= "PREVIOUS SUNDAY DATE"
      AND
      [Date] &lt;= "SATURDAY DATE (YESTERDAY)"
  </code></pre>
  
  <p>I would like to get some help with the following line:</p>
  
  <pre><code>    [Date] &gt;= "PREVIOUS SUNDAY DATE"
      AND
      [Date] &lt;= "SATURDAY DATE (YESTERDAY)"
  </code></pre>
  
  <p>So if I run the SP on <code>5/4/2014</code>, it will grab between Sunday (<code>4/27/2014</code>) to Saturday (<code>5/3/2014</code>)</p>
  
  <p>Will this work:</p>
  
  <pre><code>INSERT INTO [Database].[dbo].[table]
  SELECT 
      CONVERT(VARCHAR(10), GETDATE(), 101) AS [Date], 
      SUM([EMR ORDERS]) AS LastWeekEMROrders, 
      SUM([ACCESSIONED LAB ORDERS]) AS LastWeekAccLabOrders, 
      SUM([LAB_RESULT]) AS LastWeekLabResults
  FROM [database].[dbo].[origtable]
  WHERE
      [Date] &gt;= DATEADD(day, -((DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7) - 6,
                DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) --PREVIOUS SUNDAY
      AND
      [Date] &lt;= DATEADD(day, -(DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7,
                DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) --PREVIOUS SATURDAY
  </code></pre>
  
  <p>If I have the following query and execute it on 5/4/2014:</p>
  
  <pre><code>set @startdate = DATEADD(wk, DATEDIFF(wk, 0, getdate()), -2) --for sunday
  set @enddate = DATEADD(wk, -1, DATEADD(wk, DATEDIFF(wk, 0,getdate()), -1))-- for saturday
  </code></pre>
  
  <p>What will be the sunday's and saturday's date?</p>