title: selecting a date n days ago excluding weekends
body: <p>I have a table with daily dates starting from 31st December 1999 up to 31st December 2050, excluding weekends.</p>
  
  <p>Say given a particular date, for this example lets use 2019-03-14. I want to pick the date that was 30 days previous (the number of days needs to be flexible as it won't always be 30), ignoring weekends which in this case would be 2019-02-01. </p>
  
  <p>How to do this?</p>
  
  <p>I wrote the query below &amp; it indeed lists 30 days previous to the specified date.</p>
  
  <pre><code> select top 30 Date 
   from DateDimension
   where IsWeekend = 0 and Date &lt;= '2019-03-14'
   order by Date desc
  </code></pre>
  
  <p>So I thought I could use the query below to get the correct answer of 2019-02-01</p>
  
  <pre><code> ;with ds as
   ( 
      select top 30 Date 
      from DateDimension
      where IsWeekend = 0 and Date &lt;= '2019-03-14'
   )
   select min(Date) from ds
  </code></pre>
  
  <p>However this doesn't work. It returns me the first date in my table, 1999-12-31.</p>
  
  <pre><code> 2019-03-14
   2019-03-13
   2019-03-12
   2019-03-11
   2019-03-08
   2019-03-07
   2019-03-06
   2019-03-05
   2019-03-04
   2019-03-01
   2019-02-28
   2019-02-27
   2019-02-26
   2019-02-25
   2019-02-22
   2019-02-21
   2019-02-20
   2019-02-19
   2019-02-18
   2019-02-15
   2019-02-14
   2019-02-13
   2019-02-12
   2019-02-11
   2019-02-08
   2019-02-07
   2019-02-06
   2019-02-05
   2019-02-04
   2019-02-01
  </code></pre>