7 Replies Latest reply on Jul 14, 2020 10:09 PM by BeefStu

    Comparing schedule time to actual time

    BeefStu

      I'm looking for some help trying to compare a schedule to actual time an employee swiped their card at a location.

       

      I have 3 tables schedule_assignment, schedule_detail and access_history.

       

      The schedule_detail contains a list of times and locations.

       

      The schedule_assignment assigns a list to an employee.

       

      The access_history table has information (employee_id, location_id,

      DATETIME)

      whenever an employee swipes their card.

       

      What I need to do is match the scheduled employee_id, location_id and scheduled time against a history record to determine if the employee was on time (good), early, late, out of range or never swiped their card at that location.

       

       

       

      A status row is considered good if the access_history and trunc DATE rows and then walk through the schedule records and find the corresponding match HHMMSS from access_history

       

      Since the chances of finding an exact match on the dates are very slim, I need to look through time period of the rows.

       

      This way I can determine if an employee was on time, early, late, out of range or never swiped their card at that location.

       

      A status is on time if the history record  is + or -2 minutes of the schedule time, considered early if  between 2 minutes 1 second or 4 minutes before the schedule time, late if between 2 minutes 1 second or 4 minutes after the schedule time, out of range if 4 minutes 1 second before or after the scheduled time.

       

      I took a stab at it with this initial query to provide a visual on what I was trying to do. I know its wrong the part regarding the time is incorrect but I wanted to leave it as a place holder.

       

      I could use help with a few things. How to compare the scheduled time to the actual time to generate the status as mentioned above.

       

      Second, how do I handle the case of a missing history record.

       

      Note, the schedule will ALWAYS be in chronological order.

       

       

      SELECT sa.employee_id,  sd.location_id,

      sd.schedule_date,

      ah.access_date

       

      FROM

      schedule_assignment sa,

      schedule_detail sd,

      access_history ah

      WHERE

      sa.employee_id =

      ah.employee_id AND

      sd.location_id =

      ah.location_id AND

      -- this part is wrong!!!

       

      (ah.access_date IS NULL OR

      TRUNC(sd.schedule_date) = TRUNC (ah.access_date) 

      );

       

      Below is a detailed test case with the expected outcome and a detailed explanation for each row.

       

      ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

       

      CREATE table schedule_hdr(

        schedule_id NUMBER(4),

        schedule_name VARCHAR2(30)

      );

       

      INSERT INTO schedule_hdr(

        schedule_id,

        schedule_name

      )

      VALUES

      (5, 'Thursday Day Shift');

       

       

      CREATE table schedule_assignment(

         schedule_id NUMBER(4),

         employee_id NUMBER(6)

      );

       

      INSERT INTO schedule_assignment(

        schedule_id,

        employee_id  

      )

      VALUES (5,1);

       

       

      create table schedule_detail(

             schedule_id NUMBER(4),

             location_id number(4),

             schedule_date date

        );

       

      insert into schedule_detail(

        schedule_id,

        location_id,

        schedule_date)

      values

        (5, 100,

      TO_DATE('2020/07/23 11:00:00', 'yyyy/mm/dd hh24:mi:ss'));

       

      insert into schedule_detail(

      schedule_id,

      location_id,  schedule_date)

      values

        (5, 101,

      TO_DATE('2020/07/23 11:04:00', 'yyyy/mm/dd hh24:mi:ss'));

       

      insert into schedule_detail(

      schedule_id,

      location_id,  schedule_date)

      values

        (5,102,

      TO_DATE('2020/07/23 11:07:00', 'yyyy/mm/dd hh24:mi:ss'));

       

      insert into schedule_detail(

      schedule_id,

      location_id,  schedule_date)

      values

        (5,103,

      TO_DATE('2020/07/23 11:10:00', 'yyyy/mm/dd hh24:mi:ss'));

       

       

      insert into schedule_detail(

      schedule_id,

      location_id,  schedule_date)

      values

        (5,100,

      TO_DATE('2020/07/23 11:11:00', 'yyyy/mm/dd hh24:mi:ss'));

       

          create table access_history(

             employee_id NUMBER(6),

             location_id number(4),

             access_date date

          );

       

      insert into access_history(

      employee_id,

      location_id,  access_date)

      VALUES

        (1, 100,

      TO_DATE('2020/07/23 11:00:20', 'yyyy/mm/dd hh24:mi:ss'));

       

       

      insert into access_history(

      employee_id,

      location_id,  access_date)

      VALUES

        (1, 101,

      TO_DATE('2020/07/23 11:01:53', 'yyyy/mm/dd hh24:mi:ss'));

       

      insert into access_history(

      employee_id,

      location_id,  access_date)

      values

        (1, 102,

      TO_DATE('2020/07/23 11:10:13', 'yyyy/mm/dd hh24:mi:ss'));

       

       

      insert into access_history(

      employee_id,

      location_id,  access_date)

      values

        (1,103,

      TO_DATE('2020/07/23 11:15:12', 'yyyy/mm/dd hh24:mi:ss'));

       

       

      -- results of query should populate this table.

       

      CREATE table schedule_history(

       

            seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,

               employee_id NUMBER(6),

             schedule_id NUMBER(4),

             location_id number(4),

             schedule_date date,

            access_date date,

            status VARCHAR2(1)

      );

       

       

      SELECT * from schedule_history

       

      SEQ_NUM    EMPLOYEE_ID   

      SCHEDULE_ID LOCATION_ID    SCHEDULE_DATE    ACCESS_DATE    STATUS

      1    1    5 100    07232020 11:00:00    07232020 11:00:20    G

       

      -- This row GOOD because only 20 second difference from schedule

       

      2    1    5 101    07232020 11:04:00    07232020 11:01:53    E

       

      -- This row early because because 2 minutes 7 seconds before schedule time

       

      3    1    5 102    07232020 11:07:00    07232020 11:10:13    L

       

      -- This row late because because 3 minutes 13 seconds after schedule time

       

      4    1    5 103    07232020 11:10:00    07232020 11:15:12    O

       

      -- This is out of range because its 5 minutes and 12 seconds after the scheduled time. Note it could be out of range if its earlier then the scheduled time too.

       

       

      5    1    5 100    07232020 11:11:00     NULL     N

       

      -- This row is NO GOOD as there is no corresponding access_history record. Note the NULL value for access_date

        • 1. Re: Comparing schedule time to actual time
          jaramill

          You've been mentioned this before....please follow guideline #5 from the link on --> Re: 2. How do I ask a question on the forums?

           

          5) Database Version and IDE Version
          Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.

           

          If you're not sure what it is you can do the following:

          select * from v$version;

          in an SQL*Plus session and paste the results.

          • 2. Re: Comparing schedule time to actual time
            Frank Kulash

            Hi,

             

            Thanks for posting the sample data and desired results.

            It never makes sense for a column in any table to have the same value on every row (as access_history.employee_id = 1 on every row of your sample data, to give just one example).  The real tables will have multiple values, so you need to test that solutions are handling multiple values correctly.

             

            So, you need to get rows from schedule_detail whether or not they have matching rows in access_history.  That sounds like a job for an outer join, like this:

            WITH    sd    AS

            (

                SELECT  schedule_id, location_id, schedule_date

                ,       ROW_NUMBER () OVER ( PARTITION BY  schedule_id, location_id, TRUNC (schedule_date)

                                             ORDER BY      schedule_date

                                           )                 AS rn

                ,       TRUNC (schedule_date)                AS this_day

                ,       TRUNC (schedule_date) + 1            AS next_day

                ,       schedule_date - INTERVAL '4' MINUTE  AS very_early_date

                ,       schedule_date - INTERVAL '2' MINUTE  AS early_date

                ,       schedule_date + INTERVAL '2' MINUTE  AS late_date

                ,       schedule_date + INTERVAL '4' MINUTE  AS very_late_date

                FROM    schedule_detail

            )

            ,    ah    AS

            (

                SELECT  employee_id, location_id, access_date

                ,       ROW_NUMBER () OVER ( PARTITION BY  employee_id, location_id, TRUNC (access_date)

                                             ORDER BY      access_date

                                           )       AS rn

                FROM    access_history

            --  WHERE   ...  -- if you need any filtering, put it here

            )

            SELECT    ROW_NUMBER () OVER ( PARTITION BY  sa.employee_id

                                           ORDER BY      sd.schedule_date

                                         )  AS seq_num

            ,         sa.employee_id

            ,         sd.schedule_id, sd.location_id, sd.schedule_date

            ,         ah.access_date

            ,         CASE

                          WHEN  ah.access_date  <  sd.very_early_date  THEN  'O'

                          WHEN  ah.access_date  <  sd.early_date       THEN  'E'

                          WHEN  ah.access_date  <= sd.late_date        THEN  'G'

                          WHEN  ah.access_date  <= sd.very_late_date   THEN  'L'

                          WHEN  ah.access_date  >  sd.very_late_date   THEN  'O'

                          WHEN  ah.access_date  IS NULL                THEN  'N'

                      END  AS status

            FROM      schedule_assignment  sa

            JOIN                           sd  ON   sd.schedule_id  = sa.schedule_id

            LEFT OUTER JOIN                ah  ON   ah.employee_id  = sa.employee_id

                                               AND  ah.location_id  = sd.location_id

                                               AND  ah.access_date  >= sd.this_day

                                               AND  ah.access_date  <  sd.next_day

                                               AND  ah.rn           =  sd.rn

            ORDER BY  sa.employee_id

            ,         sd.schedule_date

            ;

            I recommend ANSI join syntax for all joins, especially outer joins.

            Results from your sample data:

            SEQ EMPLOYEE SCHEDULE LOCATION

            _NUM      _ID      _ID      _ID SCHEDULE_DATE       ACCESS_DATE         STATUS

            ---- -------- -------- -------- ------------------- ------------------- ------

               1        1        5      100 2020/07/23 11:00:00 2020/07/23 11:00:20 G

               2        1        5      101 2020/07/23 11:04:00 2020/07/23 11:01:53 E

               3        1        5      102 2020/07/23 11:07:00 2020/07/23 11:10:13 L

               4        1        5      103 2020/07/23 11:10:00 2020/07/23 11:15:12 O

               5        1        5      100 2020/07/23 11:11:00                     N

            In this example, you seem to assume that employee 1 was on time for the 11:00 check-in at location 100 and missed the 11:11 check_in at that same location.  How do you know he didn't miss the 11:00 check at that location, and was very early for the 11:11 one?

            • 3. Re: Comparing schedule time to actual time
              BeefStu

              Thanks for your help and response. I wanted to keep the test CASE easy to follow and succinct so it would be easy to follow for those who answer. My intention is to add more data, indexes... once I had a solution.

               

              As for your question about employee_id=1 11:00 I had thought about that too but was unsure how to solve that problem. My thinking is since the schedule will ALWAYS be in chronological order and a previous match was already found that would leave  the 11:11 entry as the unmatched row. I know it's a poor assumption but I didn't know how to work around it. I am open to any and all ideas on how to handle that scenario.

               

              Its almost like giving an answer without knowing the question. Ie if I say  4, someone may respond and say the question is 2+2 when the question is 5-1. If I left that row out how would anyone know there was a problem so I figured an assumption was better than nothing

              • 4. Re: Comparing schedule time to actual time
                BeefStu

                I tried making a design change and removed the schedule_assignment table and now keep the employee_id in the schedule_detail table.

                 

                This way an employee isn't tied to the same schedule.

                 

                I tried modifying your code for the past day to fix my design change and can't seem to get it to work. I was wondering if you could take a look if you have a chance.

                 

                I'm getting the following error

                 

                ORA-00904: "SD"."RN": invalid identifier

                 

                Below is the modified test CASE and my attempt at the change. The results should be exactly the same as when I posted this question originally.

                Thanks in advance for your time, patience and expertise.

                 

                 

                create table schedule_detail(

                       schedule_id NUMBER(4),

                       employee_id NUMBER(6),

                       location_id number(4),

                       schedule_date date

                  );

                 

                 

                insert into schedule_detail(

                  schedule_id,

                  employee_id,

                  location_id,

                  schedule_date)

                values

                  (5,1, 100,

                TO_DATE('2020/07/23 11:00:00', 'yyyy/mm/dd hh24:mi:ss'));

                 

                insert into schedule_detail(

                schedule_id,

                employee_id,

                location_id,  schedule_date)

                values

                  (5, 1, 101,

                TO_DATE('2020/07/23 11:04:00', 'yyyy/mm/dd hh24:mi:ss'));

                 

                insert into schedule_detail(

                schedule_id,

                employee_id,

                location_id,  schedule_date)

                values

                  (5,1,102,

                TO_DATE('2020/07/23 11:07:00', 'yyyy/mm/dd hh24:mi:ss'));

                 

                insert into schedule_detail(

                schedule_id,

                employee_id,

                location_id,  schedule_date)

                values

                  (5,1,103,

                TO_DATE('2020/07/23 11:10:00', 'yyyy/mm/dd hh24:mi:ss'));

                 

                 

                insert into schedule_detail(

                schedule_id,

                employee_id,

                location_id,  schedule_date)

                values

                  (5,1,100,

                TO_DATE('2020/07/23 11:11:00', 'yyyy/mm/dd hh24:mi:ss'));

                 

                    create table access_history(

                       employee_id NUMBER(6),

                       location_id number(4),

                       access_date date

                    );

                 

                insert into access_history(

                employee_id,

                location_id,  access_date)

                VALUES

                  (1, 100,

                TO_DATE('2020/07/23 11:00:20', 'yyyy/mm/dd hh24:mi:ss'));

                 

                 

                insert into access_history(

                employee_id,

                location_id,  access_date)

                VALUES

                  (1, 101,

                TO_DATE('2020/07/23 11:01:53', 'yyyy/mm/dd hh24:mi:ss'));

                 

                insert into access_history(

                employee_id,

                location_id,  access_date)

                values

                  (1, 102,

                TO_DATE('2020/07/23 11:10:13', 'yyyy/mm/dd hh24:mi:ss'));

                 

                 

                insert into access_history(

                employee_id,

                location_id,  access_date)

                values

                  (1,103,

                TO_DATE('2020/07/23 11:15:12', 'yyyy/mm/dd hh24:mi:ss'));

                 

                 

                CREATE table schedule_history(

                 

                      seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,

                         employee_id NUMBER(6),

                       schedule_id NUMBER(4),

                       location_id number(4),

                       schedule_date date,

                      access_date date,

                      status VARCHAR2(1)

                );

                 

                 

                WITH  sd  AS

                (

                 

                    SELECT  schedule_id, employee_id,  location_id, schedule_date

                    ,       ROW_NUMBER () OVER ( PARTITION BY  schedule_id, employee_id, location_id, TRUNC (schedule_date)

                        ORDER BY      schedule_date

                   )      AS rn

                    ,       TRUNC (schedule_date)                AS this_day

                    ,       TRUNC (schedule_date) + 1            AS next_day

                    ,       schedule_date - INTERVAL '4' MINUTE  AS very_early_date

                    ,       schedule_date - INTERVAL '2' MINUTE  AS early_date

                    ,       schedule_date + INTERVAL '2' MINUTE  AS late_date

                    ,       schedule_date + INTERVAL '4' MINUTE  AS very_late_date

                    FROM    schedule_detail

                )

                ,    ah    AS

                (

                 

                    SELECT  employee_id, location_id, access_date

                 

                    ,       ROW_NUMBER () OVER ( PARTITION BY  employee_id, location_id, TRUNC (access_date)

                 

                          ORDER BY      access_date

                 

                    )     AS rn

                    FROM    access_history

                --  WHERE   ...  -- if you need any filtering, put it here

                )

                 

                SELECT    ROW_NUMBER () OVER ( PARTITION BY  sd.employee_id

                 

                  ORDER BY  sd.schedule_date

                 

                )  AS seq_num

                ,         sd.employee_id

                ,         sd.schedule_id, sd.location_id, sd.schedule_date

                ,         ah.access_date

                ,         CASE

                              WHEN  ah.access_date  <  sd.very_early_date  THEN  'O'

                              WHEN  ah.access_date  <  sd.early_date       THEN  'E'

                              WHEN  ah.access_date  <= sd.late_date        THEN  'G'

                              WHEN  ah.access_date  <= sd.very_late_date   THEN  'L'

                              WHEN  ah.access_date  >  sd.very_late_date   THEN  'O'

                              WHEN  ah.access_date  IS NULL                THEN  'N'

                          END  AS status

                 

                FROM      schedule_detail sd

                LEFT OUTER JOIN                ah  ON   ah.employee_id  = sd.employee_id

                                                   AND  ah.location_id  = sd.location_id

                                                   AND  ah.access_date  >= sd.this_day

                                                   AND  ah.access_date  <  sd.next_day

                                  AND  ah.rn           =  sd.rn

                ORDER BY 

                sa.employee_id, sd.schedule_date;

                • 5. Re: Comparing schedule time to actual time
                  Frank Kulash

                  Hi,

                   

                  You almost had it!

                   

                  Look at the FROM clause of the main query in reply #4 (let's call it Q4):

                  FROM      schedule_detail sd

                  The alias sd is applied to the original schedule_detail table.  The sub-query called sd, which has the derived columns, such as rn, is never used.

                   

                  Change that line to

                  FROM             sd

                  just like it was in reply #2.

                   

                  Also, there is no table called sa in Q4.  In the ORDER BY clause, I think you meant:

                  ORDER BY  sd.employee_id, sd.schedule_date;

                  • 6. Re: Comparing schedule time to actual time
                    Frank Kulash

                    By the way:

                    BeefStu wrote:

                     

                    I tried making a design change and removed the schedule_assignment table and now keep the employee_id in the schedule_detail table.

                     

                    This way an employee isn't tied to the same schedule. ...

                    An employee wasn't tied to the same schedule in the old design, either. 

                    What's different about the two designs is that originally, a schedule wasn't tied to a particular employee; in the new design, it is.

                    • 7. Re: Comparing schedule time to actual time
                      BeefStu

                      Thanks, for the help. I should have seen that. Ugggh