Query - do I need temp table?

Dear experts -
I have a query in which I summarize weekly totals of hours by employee.
Of course, some weeks, employees will not have a total.
I want to show ALL weeks in a given period, and then the total for that person.

I imagine I can do this using a temp table populated with one record for each week in question, and then do a join with the query of totals. Thus, I could show all weeks, and if the total is zero for that person in a given week, it will show zero.

My question is whether there's a better/simpler way, without using a temp table.

Thanks -
terpsichoreAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
This technically isn't a temp table since it isn't deleted and recreated.  When working with a large enough population, you are likely to have an entry for every week.  However, when working with a single individual, there will be gaps so you do need a way to provide structure so the "gaps" can be replaced with 0.  

Build a table with the week starting and ending dates (this also solves the problem of how you handle year crossover weeks.  Then every November (or whenever makes sense), add another year to the table.
0
 
ste5anSenior DeveloperCommented:
Well, yes and no. It's a common practice to use a calendar table. Then you can use this table to join in your data. So you need a table, but not a temporary one.

Simply prefil the table with five or ten years - just ensure that it covers your current and near future use-cases. Such a table will not take much space.
0
 
terpsichoreAuthor Commented:
thanks so much (this answer was first).
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
IrogSintaCommented:
Another option is to create a table with 52 records numbered from 1 to 52.  These are your week numbers.  You won't ever have to add anymore to this table.  Then you can use a query that will always provide the correct beginning and ending dates for each week no matter what year it is.  You will use this query to join with your existing table.  Here's what the SQL for the query should be:
SELECT WeekNum, DateAdd("ww",[weeknum]-1,DateSerial(Year(Date()),1,1))-Weekday(DateSerial(Year(Date()),1,1),1) AS BegWeek, [BegWeek]+7 AS EndWeek
FROM tblWeeks
The above will give you the following:
imgThe week range is from Sunday to Saturday.  If you wanted your week to start from another day, you would change the number 1 which I have highlighted in bold in the query to a number between 1 to 7.  For instance, change it to 2 if your week is from Monday to Sunday.

Ron
0
 
terpsichoreAuthor Commented:
many thanks, great idea
0
 
ste5anSenior DeveloperCommented:
@Ron: It's better to create a full calendar, instead a condensed one. Just store these as additional columns.
0
 
IrogSintaCommented:
@ste5an
Come to think of it, it would be better to use a full table since the query I provided would only work for the current year.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.