Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
Thanks, I will look into that.