SQL / CFM question regarding hourly scheduling

Posted on 2005-04-12
Last Modified: 2013-12-24
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.

Question by:SiriusPhil
    LVL 35

    Expert Comment

    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

    Author Comment

    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.
    LVL 35

    Expert Comment

    Can a time off be cross shift?
    LVL 35

    Expert Comment


    17:00 - 20:00

    or would it be entered as

    17:00 - 18:00

    18:00 - 20:00

    Author Comment

    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).
    LVL 35

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Article by: kevp75
    Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
    Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now