# Generate date records for each weekday dynamically

• ### 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

• 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 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 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