Solved

Query - do I need temp table?

Posted on 2015-02-01
7
181 Views
Last Modified: 2016-02-11
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 -
0
Comment
Question by:terpsichore
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40582604
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
 
LVL 33

Expert Comment

by:ste5an
ID: 40582605
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
 

Author Closing Comment

by:terpsichore
ID: 40582616
thanks so much (this answer was first).
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 29

Expert Comment

by:IrogSinta
ID: 40582646
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
 

Author Comment

by:terpsichore
ID: 40583440
many thanks, great idea
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40585810
@Ron: It's better to create a full calendar, instead a condensed one. Just store these as additional columns.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40586162
@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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question