Looking for the most efficient way to get total of weekly hours.

Hi Experts,

I need to get a total of hours scheduled per employee of the current week.

I have two options.

Option 1:

Having a table named PatientsEmployeesSchedule containing actual schedules, with the following columns

EmployeeID (Column is indexed)
Day (Column is indexed)
From (Start time of schedule)
To (End time of schedule)

Option 2:

Now I can also  get it from the Patients/Employees Allowances.

There I have 2 Tables

EffectiveFrom (Date From When does it start)
EffectiveTo (Date finishes allowance)
EndStatusDate (If this is filled it overrides date of EffectiveTo)

PatientsBillingCodesID (Links to ID of PatientsBillingCodes_HC)
TimeFrom (Time start of schedule, like 9:00 AM)
TimeTo (Time end of Schedule, like 5:00 PM)
WeekDay (Number field, 1 for Sun, 2 for Mon etc.)

The first option involves a table with a large quantity of records while the second option have to join two tables and do some logical calculations to get the current week.

A) What would be my best option regarding efficiency?

B) And which are the most efficient SQL’s?

Perhaps I can try both SQL’s  and see in SSMS which is being executed faster, or try to analyze the execution plans.

PS, Re second option, see attached for table fields & relationship, (they are SQL tables linked to MS Access).

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Isn't the first one actual hours and the second one scheduled?  So, the answer is, it depends on what you want.  In the first example though, I would not have a separate column for date.  I would use the datetime datatype and include both in one column so you can do date arithmetic easily.
bfuchsAuthor Commented:

The way we have it is that there is a place where we keep the outline of employees schedule per week, and from there it gets created in a weekly/monthly bases schedules (thru an append query).

The first option is the actual schedule that got created, while the second option is the outline of the schedule.

However in this case, both results will work for what we need, as the purpose is to get a list of all employees who are assigned less then X amount of hours the current week, we will use the most efficient method.

Since you want scheduled hours rather than actual hours, use the second method.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bfuchsAuthor Commented:
The problem is that I am concerned this new filter option users want to add will perform slow, and therefore I'm trying at least to program with the most efficient query/view.

As per users the differences of both options are minor, so it will not matter for them from where do I take it.
Vitor MontalvãoMSSQL Senior EngineerCommented:
In the Option 1 why you have a Day column? From and To aren't date fields?
Also in the Option 2 why you have a Weekday column? You can always know the weekday of a date.
So, for the Option 1 you should have a similar query to this one:
SELECT EmployeeID , DATEPART(ww,from) WeekNumber, SUM(DATEDIFF(hh,from,to)) WorkedHours
FROM PatientsEmployeesSchedule 
GROUP BY EmployeeID , DATEPART(ww,from)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Option 1 will ultimately reference many more rows than option 2 which is why I would use option 2.  

Access forms connected to SQL Server BE's should not be using filters.  You should have search boxes on the form that are referenced by the form's RecordSource query and then when the user enters his criteria, you requery the form to return only the requested records.  As long as you use indexes appropriately, this method will not bog down.

Weekday is in the second option because it is a schedule.  Since option 2 is a schedule, it does not include a specific date.  Person A is scheduled to work certain hours on certain week days.  Option 1 is the record of the actual work and so includes the date of the work.
bfuchsAuthor Commented:
Hi Experts,

First excuse me for not having replied, as I just returned of my vacation.

The day column contains the date (05/26/15) while the from/to columns contain times, I set that up this way so user only needs to enter day only once, as the entire record belongs to one day only.
I will test your query option & let you know how it works.

Not sure why you're stating that option 1 will return more records, since 90% of the records there comes from tables in Option2 (as append query) and the rest are usually just modifications not additions.

Another point I didn't mention yet, the tables in option2 are also limits of how many times they're allowed to be scheduled per week, therefore I would only be concerned on the users getting the results in efficient manner.

Thanks for pointing out the filter issue, this is something I always had in mind to do but never materialized, as some tables involved there are (still) Access BE based..Once I up-size them I guess the best way would be to have the form based in a pass-through query and modify the query's SQL property by code according to what user choose to filter.

PS. the response to Vitor's question is correct.

bfuchsAuthor Commented:
Working well,
Thank you experts!
As you described option 1, it is a record of time worked.  Therefore every day someone works, adds a new record.  Option 2 is a schedule and so only exists as a single record.  The only time new records get added is if the schedule changes.

Pass through queries are not updateable so unless you are prepared to take on that coding effort, stick with your Access queries for bound forms.  Just make sure they use criteria to limit the rows returned.  You don't want to bind forms to queries without criteria or directly to tables since that creates an open pipeline to the server where Access requests all table rows to be returned.  The whole point of using SQL server is to minimize network traffic and that is why our technique for "filtering" changes.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.