[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

SQL / CFM question regarding hourly scheduling

ok.  This may be a bit confusing...

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

Employee 1      
Employee 2    
Employee 3    
Employee 4    
Employee 5  
Employee 6    

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.
Employee 2   08:00-12:00  
Employee 4   14:00-18:00
Employee 6   08:00-18:00 (all day off)

What I need to show is this... I list of employees and the hours they are working.  

Employee 1     08:00-18:00
Employee 2     12:00-18:00 *
Employee 3     08:00-18:00
Employee 4     08:00-14:00**
Employee 5     08:00-18:00

* 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?

Thanks in advance.

  • 4
  • 2
1 Solution
How is this data stored?  If stored as start time end time then maybe.

Also can someone have a partial shift off mid shift?

Employee 1    09:00 - 10:00
SiriusPhilAuthor Commented:
1.  All are stored as date/time (smalldattime) fields.

2.  Yes,  there could be someone off mid shift as in the example given above.

3.  Also, the work schedule also goes around the clock.  So while the day shift runs from 08:00 to 18:00, the second shift runs from 18:00 to 24:00 hours.
Can a time off be cross shift?
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


17:00 - 20:00

or would it be entered as

17:00 - 18:00

18:00 - 20:00
SiriusPhilAuthor Commented:
No. There will be no cross shift entries in table #1 or #2.  All requests for time off will reside in shift one or shift two. FYI...   Shift one is from 08:00 to 18:00.  Shift two is from 18:00 to 08:00 (the following day).
a few last questions
--> can you have multiple times off in one shift.  i.e.  8:00 - 10:00 and 16:00 - 18:00 for the same employee?  
--> can times start off the hour i.e. 8:15

here is some rough code assuming employees have only one break per shift  (also 24:00 is represented as 00:00)

<cfquery name="getworkers" datasource="yourdsn">
SELECT employeesworking.EmployeeID, shiftNum, offstart, offend
FROM employeesworking LEFT OUTER JOIN employeesoff ON employeesworking.EmployeeID = employeesoff.employeeID
ORDER BY shiftnum

<cfoutput query="getworkers" group="shiftnum">
<div>Employees working shift #shiftnum#<br>
Employee #EmployeeID#:
<cfif shiftnum EQ 1>
     <cfset shiftstart = CreateDateTime(Year(Now()), Month(Now()), Day(Now()), 8, 0, 0)>
     <cfset shiftend = CreateDateTime(Year(Now()), Month(Now()), Day(Now()), 18, 0, 0)>
     <cfif DateCompare(shiftstart, offstart, "h") NEQ 0>
         #shiftstart# -
          <cfif DateCompare(offstart, shiftend, "h") EQ -1>
                <cfif DateCompare(offend, shiftend, "h") EQ -1>
                      and #offend# - #shiftend#
     <cfelseif DateCompare(offend, shiftend, "h") EQ -1>
           #offend# - #shiftend#
          Scheduled to work, but off whole shift
     <cfset shiftstart = CreateDateTime(Year(Now()), Month(Now()), Day(Now()), 18, 0, 0)>
     <cfset shiftend = CreateDateTime(Year(DateAdd("d", 1, Now())), Month(DateAdd("d", 1, Now())), Day(DateAdd("d", 1, Now())), 8, 0, 0)>
     <!--- do similar logic for second shift as I did in first shift --->

I haven't tested this, but I am guessing it should work with some slight modification.  At least if may get you started.

Featured Post

Hire Technology Freelancers with Gigs

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.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now