I need to get a total of hours scheduled per employee of the current week.
I have two options.
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)
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).