asked on

union query (I assume)

Dear experts -
I have 1 table ('timesheet_summary') that contains a date (always a Monday), a person ID (numeric), a total hours, and a few other fields.
I have another table (calendar table) that contains all Mondays.
What I want to do is populate a new table (temp table) that, for a given period (I would like to have the flexibility of specifying this) would generate one record for every Monday; it would grab all records that exist in the 'timesheet_summary' table FOR THAT PERSON, and would create a new record for every Monday in that period that didn't exist in the 'timesheet_summary' table.
EXAMPLE:
person = '13'; date range = from 2 March 2015 to 30 Marc h 2015 (five Mondays)
timesheet_summary table contains these records
2 March 2015; person = 13; total = 10 hours
16 March 2015; person = 13; total = 8 hours

Now, when I run this query (union query?) the output would be:
2 March 2015; person = 13; total = 10 hours
9 March 2015; person = 13
16 March 2015; person = 13; total = 8 hours
23 March 2015; person = 13
30 March 2015; person = 13

Any suggestions are appreciated...

aikimark

You can do this with a tally table (numbers table). I show an example of generating dates in this article:
http:A_5410.html

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

if that calendar table does contain all Mondays then the left join will be sufficient (as proposed by chaau)

if that calendar table has gaps, then a left join will not be sufficient and you will need to generate the Mondays

terpsichore

ASKER

this seems to do the trick. thank you.