title: Find all records (rows) for most recent day of business
body: <p>I'm trying to write a T-SQL script that will be scheduled to run automatically. It should return records relating to the most recent full day of business, i.e. yesterday or, when run on a Monday, last Friday.</p>
  
  <p>I've been asked to provide a report of the number of all open cases at the start of the most recent full day of business (i.e., yesterday or last Friday and the code below is my firs attempt at that, but it doesn't work when run on Monday), as well as all cases opened, all cases closed, and all open cases at the end of the day. This is what I have so far, it provides the opening balance (but it only works when run on Tuesday through Friday... not on Monday):</p>
  
  <pre><code>DECLARE @Start DATE = (getdate()-1), @end DATE = getdate()
  
  SELECT
        tc_sk -- the ID
        ,[tc_open_date]
        ,[tc_closed_date]
    FROM tbl_cases
    WHERE tc_open_date&lt;@start
    AND
    tc_closed_date&gt;=@start
    OR
    tc_open_date&lt;@start
    AND
    tc_closed_date is null
  </code></pre>
  
  <p>Again, the problem I'm trying to solve is getting the a list of all the records that were opened during the last day of business. If the script runs on Monday, it should retrieve records entered last Friday. If the script runs Tuesday through Friday, it should retrieve records entered "yesterday".</p>
  
  <p>I'm pretty new at scripting and I'm self taught. Any help you can give me is greatly appreciated!</p>