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
1)PatientsBillingCodes_HC
ID (PK)
EffectiveFrom (Date From When does it start)
EffectiveTo (Date finishes allowance)
EndStatusDate (If this is filled it overrides date of EffectiveTo)
2)PatientsBillingCodesDate
PatientsBillingCodesID (Links to ID of PatientsBillingCodes_HC)
EmployeeID
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).
Thanks.
untitled.bmp
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
1)PatientsBillingCodes_HC
ID (PK)
EffectiveFrom (Date From When does it start)
EffectiveTo (Date finishes allowance)
EndStatusDate (If this is filled it overrides date of EffectiveTo)
2)PatientsBillingCodesDate
PatientsBillingCodesID (Links to ID of PatientsBillingCodes_HC)
EmployeeID
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).
Thanks.
untitled.bmp
PatHartman
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.
ASKER
Hi,
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.
Thanks,
Ben
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.
Thanks,
Ben
Since you want scheduled hours rather than actual hours, use the second method.
ASKER
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.
As per users the differences of both options are minor, so it will not matter for them from where do I take it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Experts,
First excuse me for not having replied, as I just returned of my vacation.
@Vitor,
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.
@Pat,
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.
Thanks,
Ben
First excuse me for not having replied, as I just returned of my vacation.
@Vitor,
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.
@Pat,
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.
Thanks,
Ben
ASKER
Working well,
Thank you experts!
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.
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.