I have two tables in a SQL database that are used by one of my clients enterprise application. I cannot modify the tables. Table 1 is a default list of people scheduled to work during certain time periods. I run a query angainst table 1 to get a list of people scheduled to work for any given time period. It basically returns their employee number and name.
So my first query for 4/11/2005 shows the following people scheduled to work between 08:00 and 18:00
I have a second table that lists all employees approved time off requests. Now here is the kicker... the time off requests can be partial shifts. So for 4/11/2005, the following people are off during the times indicated.
* Note: Since he is off 08:00-12:00, he is is working from 12:00-18:00
** Note: Since he is off 14:00-18:00, he is is working from 08:00-14:00
*** Note: Employee 6 does not show up at all becuase his is scheduled to be off for the entire shift.
My question is... how the heck do I write a query or combination of queries and CFM pages to show this?
Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.