Good day...
I am hoping someone can assist or point me in a direction with regards to managing times in a database.
Allow me to explain...
I have 3x tables...
tblHolidays which contains "HolidaysID", "HolidayName" and "HolidayDate"
tblEmployees which contains "EmployeesID", "EmployeeName" and "EmployeeLevel"
tblWorkingHours which contains "WorkingHoursID", "EmployeeName", "Date", "StartTime", "EndTime", "@1", "@1,5", "@2" and "LostTime"
What I am attempting to do is...
For level 1 Staff
On a weekday i.e. Monday thru Friday any time worked other than between 08h00 and 16h30 needs to be captured in a separate column i.e. "@1"
unless the date matches a date in tblHolidays, in this case if hours worked is less than 8 then 8 hours of worked time needs to go into a column "@1".
If hours worked is more than 8 then total hours of worked time needs to go into a column "@1".
Any hours worked on a Saturday needs to go into the "@1" column
On a Sunday, if hours worked is less than 4 but more than 0, then 4 hours will be logged in the "@" column, else total hours worked will be logged in "@1" column
For level 2 Staff
On a weekday i.e. Monday thru Friday any time worked other than between 08h00 and 16h30 needs to be captured in a separate column i.e. "@1,5"
unless the date matches a date in tblHolidays, in this case if hours worked is less than 8 then 8 hours of worked time needs to go into a column "@1".
If hours worked is more than 8 then 8 hours of worked time needs to go into a column "@1" and the rest must go into a column "@2".
Any hours worked on a Saturday needs to go into the "@1,5" column
On a Sunday, if hours worked is less than 4 but more than 0, then 4 hours will be logged in the "@2" column, else hours worked will be logged in "@2" column.
TIA
I am hoping someone can assist or point me in a direction with regards to managing times in a database.
Allow me to explain...
I have 3x tables...
tblHolidays which contains "HolidaysID", "HolidayName" and "HolidayDate"
tblEmployees which contains "EmployeesID", "EmployeeName" and "EmployeeLevel"
tblWorkingHours which contains "WorkingHoursID", "EmployeeName", "Date", "StartTime", "EndTime", "@1", "@1,5", "@2" and "LostTime"
What I am attempting to do is...
For level 1 Staff
On a weekday i.e. Monday thru Friday any time worked other than between 08h00 and 16h30 needs to be captured in a separate column i.e. "@1"
unless the date matches a date in tblHolidays, in this case if hours worked is less than 8 then 8 hours of worked time needs to go into a column "@1".
If hours worked is more than 8 then total hours of worked time needs to go into a column "@1".
Any hours worked on a Saturday needs to go into the "@1" column
On a Sunday, if hours worked is less than 4 but more than 0, then 4 hours will be logged in the "@" column, else total hours worked will be logged in "@1" column
For level 2 Staff
On a weekday i.e. Monday thru Friday any time worked other than between 08h00 and 16h30 needs to be captured in a separate column i.e. "@1,5"
unless the date matches a date in tblHolidays, in this case if hours worked is less than 8 then 8 hours of worked time needs to go into a column "@1".
If hours worked is more than 8 then 8 hours of worked time needs to go into a column "@1" and the rest must go into a column "@2".
Any hours worked on a Saturday needs to go into the "@1,5" column
On a Sunday, if hours worked is less than 4 but more than 0, then 4 hours will be logged in the "@2" column, else hours worked will be logged in "@2" column.
TIA