locked
Generate date records for each weekday dynamically RRS feed

  • Question

  • I have a query that retrieves a sum of hours estimated per day, and I'd like to represent that on a graph of weekdays with 5 columns, one for each weekdays. trouble is that the table I query, doesn't always have a record with a date for each day which results in less than five sum records for some weeks. What I need is always 5 records, with sum of 0 if the day is not in the table at all or is there but its EstimatedHours is 0.

    Here's my query:

     

    SELECT Sum(xGMV_Cotation.HoursEstimated) AS SumOfHours, datepart("WEEKDAY",[forderduedate]) AS Expr1

    FROM xGMV_Cotation

    WHERE (((DateDiff("d",[forderduedate],getDate()))>-5 And (DateDiff("d",[forderduedate],getDate()))<0) 

    GROUP BY datepart("WEEKDAY",[forderduedate])

     

    I was thinking if I outer join this with another select giving me the 5 records for weekdays of a given week, it would work. Trouble is I'm not sure how to dynamically generate those 5 records given , say a date for the Monday of such a week. I guess I could create a table with 1 date field, and populate it beforehand with every date for some 50 years (I doubt my program would be in use longer than that :), but I was wandering if there's a way to generate this dynamically.


    Lukasz
    Monday, March 28, 2011 3:01 PM

Answers

  • The way to solve such problems is to have a Calendar table and LEFT JOIN from the Calendar into your data table.

    This is a blog post explaining about Calendar tables:

    Why should I consider a Calendar table?
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by mr1uk45z Monday, March 28, 2011 3:43 PM
    Monday, March 28, 2011 3:10 PM

All replies

  • The way to solve such problems is to have a Calendar table and LEFT JOIN from the Calendar into your data table.

    This is a blog post explaining about Calendar tables:

    Why should I consider a Calendar table?
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by mr1uk45z Monday, March 28, 2011 3:43 PM
    Monday, March 28, 2011 3:10 PM
  • Thanks Naomi. Nice article.
    Lukasz
    Monday, March 28, 2011 3:44 PM