4 Replies Latest reply: Dec 8, 2013 7:58 PM by Bill Markham RSS

    Grouping events that go into the following day problem?

    Darrin Pilkington

      I am trying to flatten out an employees events for the day into a single line item in the load script.  The problem is when an employee goes over the midnight hour into the next day.  I want to have all records based on their initial login date.

       

      If I attempt to sum grouped by EmpID and Date(Floort(Datetime)) it places events into the next day which is incorrect.


      LoginSummary

      EmpID     LoginDt          LoginStart                    LoginEnd

      1234        11/15/2013     11/15/2013 22:30:00     11/16/2013 06:30:00

       

      EventDetails

      EmpID     EventID     EventDesc     DateTime                         Duration

      1234       1               Login             11/15/2013 22:30:00         0

      1234       2               Break            11/15/2013 22:20:00         14

      1234       3               Lunch            11/16/2013 01:30:00         59

      1234       4               Training         11/16/2013 02:50:00         63

      1234       2               Break            11/16/2013 03:10:00         15

      1234       5               Meeting         11/16/2013 04:50:00         37

      1234       6               Logout           11/16/2013 06:30:00         0

       

      I need to summarize the event occurrences and duration's for each employee based on the LoginDt.  Most employees work in a single day but the example shows how an agent goes into the following day.

       

      Output needs to be

       

      EmpID     EventDt          Login                         Logout                         BreakCnt     BreakDur     LunchCnt     LunchDur     etc...

      1234       11/15/2013      11/15/2013 22:30:00   11/16/2013 06:30:00     2                  29               1                 59

       

      Thank you for any help in this.

        • Re: Grouping events that go into the following day problem?
          Bill Markham

          Darrin

           

          Sounds like you are in need of the IntervalMatch() function for your Load Script.

           

          This Blog Post IntervalMatch the technical brief   IntervalMatch and Slowly Changing Dimensions.and pdf IntervalMatch and Slowly Changing Dimensions.pdf by HIC describes the IntervalMatch() function quite admirably,

           

           

          Best Regards,     Bill

          • Re: Grouping events that go into the following day problem?
            Darrin Pilkington

            It seems like I need to get the Login Date across all records for a single shift, But have it stop when the next login starts. So I can group by the date.

             

            EventDetails

            EmpID     EventID     EventDesc     DateTime                         Duration     LoginDate

            1234       1               Login             11/15/2013 22:30:00         0               11/15/2013

            1234       2               Break            11/15/2013 22:20:00         14              11/15/2013

            1234       3               Lunch            11/16/2013 01:30:00         59              11/15/2013

            1234       4               Training         11/16/2013 02:50:00         63               11/15/2013

            1234       2               Break            11/16/2013 03:10:00         15               11/15/2013

            1234       5               Meeting         11/16/2013 04:50:00         37               11/15/2013

            1234       6               Logout           11/16/2013 06:30:00         0                 11/15/2013

             

            1234       1               Login             11/16/2013 22:20:00         0               11/16/2013

            1234       2               Break            11/16/2013 22:10:00         16              11/16/2013

            1234       3               Lunch            11/17/2013 01:20:00         58              11/16/2013

            1234       2               Break            11/17/2013 03:00:00         14               11/16/2013

            1234       6               Logout           11/17/2013 06:20:00         0                 11/16/2013