title: How can I determine the date of a WeekDay column in a result set with a fixed layout given the start date?
body: <p>I have a result set that always appears in this format:</p>
  
  <pre><code>LocationId Wed Thu Fri Sat Sun Mon Tue
  </code></pre>
  
  <p>The input to the function that generates these columns accepts a start date, and it generates a 7 day quantity report.  So if I input '2014-02-01' the columns will always appear in that order even though that specific date falls on a Saturday (the dates "wrap around").</p>
  
  <p>I need the date for each column for the purpose of a calculating another value (called 'Fee') that is based on a start + end date for each location.  For example, location 21 might have a value of 50 associated with it for dates '2014-01-01' to '2014-02-03', but from '2014-02-04' it has a value of 53.  The values under the day columns refer to Sales.  So if there's a value (even 0), it means the SalesPerson was present and he should receive an AppearanceFee.  One of the difficulties is calculating exactly what Fee the person should receive on a particular day as the report doesn't generate dates.  The only information you have is the start date.</p>
  
  <p>eg.</p>
  
  <pre><code>LocationId | Value | StartDate  | EndDate
  -----------+-------+------------+-----------
  21         | 50    | 2014-01-01 | 2014-02-03
  21         | 53    | 2014-02-04 | null
  </code></pre>
  
  <p>To simulate one record, one can use this query:</p>
  
  <pre><code>declare @startdate datetime
  select @startdate = '2014-02-01'
  
  select *
  , 0 as Fee -- How do I calculate this value?
  from
  (
      select 21 as LocationId
      , 30 as Wed
      , 33 as Thu
      , 36 as Fri
      , NULL as Sat
      , NULL as Sun
      , 19 as Mon
      , 24 as Tue
  ) record
  </code></pre>
  
  <p>I've thought of using a complex case statement for each day but is there a simpler method?</p>
  
  <pre><code>CASE Left(DATENAME(dw, @startdate), 3)
  WHEN 'Wed' THEN
  (
      (SELECT IsNull(Wed, 0) * Value FROM LocationValue lv WHERE lv.LocationId = record.LocationId AND @startdate BETWEEN lv.StartDate and IsNull(lv.EndDate, '2050-12-31')) +
      (SELECT IsNull(Thu, 0) * Value FROM LocationValue lv WHERE lv.LocationId = record.LocationId AND DateAdd(dd, 1, @startdate) BETWEEN lv.StartDate and IsNull(lv.EndDate, '2050-12-31')) +
      (SELECT IsNull(Fri, 0) * Value FROM LocationValue lv WHERE lv.LocationId = record.LocationId AND DateAdd(dd, 2, @startdate) BETWEEN lv.StartDate and IsNull(lv.EndDate, '2050-12-31')) +
      (SELECT IsNull(Sat, 0) * Value FROM LocationValue lv WHERE lv.LocationId = record.LocationId AND DateAdd(dd, 3, @startdate) BETWEEN lv.StartDate and IsNull(lv.EndDate, '2050-12-31')) +
      (SELECT IsNull(Sun, 0) * Value FROM LocationValue lv WHERE lv.LocationId = record.LocationId AND DateAdd(dd, 4, @startdate) BETWEEN lv.StartDate and IsNull(lv.EndDate, '2050-12-31')) +
      (SELECT IsNull(Mon, 0) * Value FROM LocationValue lv WHERE lv.LocationId = record.LocationId AND DateAdd(dd, 5, @startdate) BETWEEN lv.StartDate and IsNull(lv.EndDate, '2050-12-31')) +
      (SELECT IsNull(Tue, 0) * Value FROM LocationValue lv WHERE lv.LocationId = record.LocationId AND DateAdd(dd, 6, @startdate) BETWEEN lv.StartDate and IsNull(lv.EndDate, '2050-12-31'))
  )
  </code></pre>
  
  <p>As you can see this case statement is rather unwieldy.</p>