Grouping events that go into the following day problem?

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Darrin

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

I did post a reply with links to a brilliant blog post  but it has got stuck in the Moderation quagmire.

Search this forum for the IntervalMatch() blog by Henric and hopefully you will find it.

Best Regards,     Bill

[Anonymous]

Thanks, I will look into that.