Hello all,
I'm building a database that stores scheduled working hours for various groups of employees. The idea is that working hours for various shifts will be stored and then a query will output to Excel the scheduled hours for each employee from a user-selected shift. So for example, it may be stored that shift A will be working from 07:00 - 14:00 from Monday to Thursday and then 07:00 - 13:30 on Fridays with Saturdays and Sundays off. If I then specify that I wish to view the scheduled working hours for every employee in shift A, for last week, I should get a spreadsheet which shows the duration in hours that should have been worked for each day (Mon-Sun,) repeated for every employee within shift A. The idea is that any discrepancies from the scheduled hours will be added to the spreadsheet and uploaded into the database and records created for the number of hours each person worked (defaulting at the scheduled number, except where something else has been specified). Not the best design ever, but it's what I've been asked to do.
I've setup the backend and populated it with some test data. The scheduled hours are stored within cycles, so Shift A has a cycle of hours in which they have longer hours every other week. They may have a cycle from say the 04/01/2010 until the 31/01/2010, and then another that starts after that and lasts for a couple of months. If a week commencing date of the 11/01/2010 is entered, the appropriate cycle is found by its date range and the hours obtained from that. I've built a query that gets the durations scheduled for each shift on each day, so it returns something like:
Shift Mon Tues Wed Thurs Fri Sat Sun
Shift A 7 7 7 6 5.5 0 0
Shift B 6 6 6 7 6 0 0
This works fine, but I have a problem with scheduled exceptions. Obviously there are exceptions to cycles such as the one I specified, for example bank holidays. My plan was to have a table that stored scheduled exceptions, such as bank holidays. Records in this table would consist of say:
ID Date Type
1 25/12/2010 2
So I want to check for each date in my query whether or not there is an exception. So into my query I've added the date for each day, so that I now have the hours for Monday, Monday's date, hours for Tuesday, Tuesday's date etc. I've the built another query to find this out. Firstly I tried adding my query and linking to seven different instances of the exceptions table via left outer joins, but this was behaving very strangely, so instead I've created seven separate queries which each return the exception day type for a given date. I've now linked these seven queries to my main query by each linking via left outer joins to a day's date. The idea being that they will only return a day's exception code, if there is a day with that date. This doesn't work though. Well it does, as long as there is an exception on the seventh day (the Sunday,) or if I remove the seventh day's query then there has to be a match on the sixth and so on, else it returns nothing at all, not even the data from the left table!
Any suggestions would be greatly appreciated.
Thanks,
Matthew
I'm building a database that stores scheduled working hours for various groups of employees. The idea is that working hours for various shifts will be stored and then a query will output to Excel the scheduled hours for each employee from a user-selected shift. So for example, it may be stored that shift A will be working from 07:00 - 14:00 from Monday to Thursday and then 07:00 - 13:30 on Fridays with Saturdays and Sundays off. If I then specify that I wish to view the scheduled working hours for every employee in shift A, for last week, I should get a spreadsheet which shows the duration in hours that should have been worked for each day (Mon-Sun,) repeated for every employee within shift A. The idea is that any discrepancies from the scheduled hours will be added to the spreadsheet and uploaded into the database and records created for the number of hours each person worked (defaulting at the scheduled number, except where something else has been specified). Not the best design ever, but it's what I've been asked to do.
I've setup the backend and populated it with some test data. The scheduled hours are stored within cycles, so Shift A has a cycle of hours in which they have longer hours every other week. They may have a cycle from say the 04/01/2010 until the 31/01/2010, and then another that starts after that and lasts for a couple of months. If a week commencing date of the 11/01/2010 is entered, the appropriate cycle is found by its date range and the hours obtained from that. I've built a query that gets the durations scheduled for each shift on each day, so it returns something like:
Shift Mon Tues Wed Thurs Fri Sat Sun
Shift A 7 7 7 6 5.5 0 0
Shift B 6 6 6 7 6 0 0
This works fine, but I have a problem with scheduled exceptions. Obviously there are exceptions to cycles such as the one I specified, for example bank holidays. My plan was to have a table that stored scheduled exceptions, such as bank holidays. Records in this table would consist of say:
ID Date Type
1 25/12/2010 2
So I want to check for each date in my query whether or not there is an exception. So into my query I've added the date for each day, so that I now have the hours for Monday, Monday's date, hours for Tuesday, Tuesday's date etc. I've the built another query to find this out. Firstly I tried adding my query and linking to seven different instances of the exceptions table via left outer joins, but this was behaving very strangely, so instead I've created seven separate queries which each return the exception day type for a given date. I've now linked these seven queries to my main query by each linking via left outer joins to a day's date. The idea being that they will only return a day's exception code, if there is a day with that date. This doesn't work though. Well it does, as long as there is an exception on the seventh day (the Sunday,) or if I remove the seventh day's query then there has to be a match on the sixth and so on, else it returns nothing at all, not even the data from the left table!
Any suggestions would be greatly appreciated.
Thanks,
Matthew
