title: SQL - Determine last day of previous week with flexibility
body: <p>I'm trying to create a flexible way to determine the last day of the previous week, knowing that this will be run in different environments that have different planning weeks (some customers use Sat-fri; Sun-Sat; Mon-Sun). I was planning on creating a variable to use as each customer's start day of the week (see below)</p>
  
  <pre><code>    Set @BeginningWeek = 1   -- 1=Mon; 2=Tues; 3=Wed; 4=Thurs; 5=Fri; 6=Sat; 7=Sun
  </code></pre>
  
  <p>I've been trying to use a combination of dateadd and datepart, along with the variable above to try to get the previous last day of the week, but I'm having trouble with the logic.  I do not want to use Set DateFirst, these scripts will be run on a customer environment, so I do not want to change anything in their DB.  </p>
  
  <p>Assuming the script is run on a wednesday (9/3); and the customer's planning week is sat-fri; I would want to set another variable @EndDate = the previous friday(8/29). I need the flexibility to use the same script (changing only the @BeginningWeek value) with another customer whose planning week is Mon-Sun and set @EndDate = the previous sunday (8/31).</p>