title: SQL - check if an order date occurs after the second Saturday in July
body: <p>I am querying against a table of 4 yrs of order transactions (pk = order number) and I'm looking to tag each record with particular date flags based on the order date - e.g., calendar year, calendar month, fiscal year, etc. There are date attributes that are specific to our business (e.g., not easily solved by a datepart function) that I'm having trouble with. </p>
  
  <p>I was able to add "School Year" (for us that runs Aug 1 - July 31) using a case statement:</p>
  
  <pre><code>case
  when datepart(month, oline.order_date_ready) between 8 and 12 then datepart(year, oline.order_date_ready)
  else (datepart(year, oline.order_date_ready)-1)
  end as school_yr
  </code></pre>
  
  <p>So for 1/19/2017, the above would return "2016", because to us the 2016 school year runs from Aug 1 2016 to July 31 2017. </p>
  
  <p>But now I'm having trouble repeating the same kind of case statement for something called "Rollover Year". All of our order history tables are reset/"rolled over" on the 2nd Saturday in July every calendar year, so for example the most recent rollover date was Saturday July 9th 2016. Click to view - <a href="https://i.stack.imgur.com/VQYqE.png" rel="nofollow noreferrer">rollover year date ranges</a> </p>
  
  <p>My above case statement doesn't apply anymore because I can't just add "datepart(month, oline.order_date_ready) = 7" - I don't need the whole month of July, I just need all the orders occurring after the 2nd Saturday in that July. So in this example, I need everything occurring from Sat July 9 2016 to today to be flagged as rollover_date = 2016. </p>
  
  <p>Is there a flexible way to do this without hard coding previous/future rollover dates into another table? That's the only way I can think to solve it currently, but I'm sure there must be a better way. </p>
  
  <p>Thanks!</p>