How can I determine the date of a WeekDay column in a result set with a fixed layout given the start date?

Question

I have a result set that always appears in this format:

LocationId Wed Thu Fri Sat Sun Mon Tue

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").

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.

eg.

LocationId | Value | StartDate  | EndDate
-----------+-------+------------+-----------
21         | 50    | 2014-01-01 | 2014-02-03
21         | 53    | 2014-02-04 | null

To simulate one record, one can use this query:

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

I've thought of using a complex case statement for each day but is there a simpler method?

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'))
)

As you can see this case statement is rather unwieldy.

this might help if you modify the start of week to be wednesday: http://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server — Tanner, May 27 '14 at 13:53
I don't think it would help because the start of the week is not always Wednesday. The start of the week is not actually important. What is important is the actual date of that particular day. — ilitirit, May 27 '14 at 14:03
you should be able to modify the linked code to specify the start of the week using a specified date — Tanner, May 27 '14 at 14:39
I was going to start in on this one, but I believe I got lost here - So the table that stores values (LocationId, Wed, Thu, etc..) doesn't store the start date? What's the structure of the table that does? Your record table can be worked with for this easily, but if you're wanting the start date in the result, I'll need a bit of a hit as to where that comes from. — Jaaz Cole, May 27 '14 at 15:30
There is no start date associated with the result set that contains the day names. All I have is that start date that was used to generated the result. It's not a table - it's the result of a view. I can't manipulate the SQL that generates the view. FWIW it's very simple to add the startdate to the result set by just wrapping it in a select statement. — ilitirit, May 27 '14 at 16:15

score 1 · Accepted Answer · answered Nov 14 '14 at 21:19

I managed to solve this using a combination of PIVOTs and UNPIVOTs, and a query that generates a date range.

DECLARE @startDate DATETIME = '2014-02-01'
DECLARE @endDate DATETIME = @startDate + 7

SELECT
  LocationId,
  Sum(Wed) Wed,
  Sum(Thu) Thu,
  Sum(Fri) Fri,
  Sum(Sat) Sat,
  Sum(Sun) Sun,
  Sum(Mon) Mon,
  Sum(Tue) Tue,
  Sum(Fee) Fee
FROM
    (
      SELECT
        af.LocationId,
        Calendar.Day,
        Date,
        Sales,
        IsNumeric(Sales) * Value AS Fee
      FROM
        (
          SELECT
            Left(DateName(DW, datetable.Date), 3) Day,
            Convert(DATE, datetable.Date)         Date
          FROM (
                 SELECT DATEADD(DAY, -(a.a + (10 * b.a) + (100 * c.a)), getdate()) AS Date
                 FROM (SELECT 0 AS a
                       UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                       UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                       UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
                   CROSS JOIN (SELECT 0 AS a
                       UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                       UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                       UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
                   CROSS JOIN (SELECT 0 AS a
                       UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                       UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                       UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
               ) datetable
          WHERE datetable.Date BETWEEN @startDate AND @endDate
        ) Calendar LEFT JOIN
        (
          SELECT
            LocationId,
            Day,
            Sales
          FROM dbo.f_FakeReport(@startDate) AS Report
          UNPIVOT
          (
              Sales
          FOR Day IN (Wed, Thu, Fri, Sat, Sun, Mon, Tue)
          ) U) AS Report
          ON Calendar.Day = Report.Day
        LEFT JOIN AppearanceFee af
          ON af.LocationId = Report.LocationId
             AND date BETWEEN af.StartDate AND IsNull(af.EndDate, '2099-12-21')
    ) data
  PIVOT
  (
    Sum(Sales)
  FOR Day IN (Wed, Thu, Fri, Sat, Sun, Mon, Tue)
  ) pvt
WHERE LocationId IS NOT NULL
GROUP BY LocationId

http://sqlfiddle.com/#!3/98759/52

How can I determine the date of a WeekDay column in a result set with a fixed layout given the start date?

1 Answers1