StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POJoining on a field with different values
text
Body
copied!<p>I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually took lunch or break). The problem is, the schedule program gives times as BREAK1, BREAK2, BREAK3, and LUNCH, while the tracking program simply lists them as Lunch and Break. I can join the data and get the lunches just fine, but the breaks are throwing me off. If I convert BREAK1, BREAK2, and BREAK3 to just "Break", I end up with too many segments because it is matching every instance with every other instance. Is there a way that anyone can think of to join these two pieces of information? Thank You.</p> <p><em>EDIT</em> At your request, here some sample data:</p> <p><strong>This is the Scheduled Times:</strong></p> <pre><code>EMP_ID NOM_DATE SEG_CODE START_MOMENT STOP_MOMENT 626009 26-Sep-13 BREAK2 9/26/13 5:00 PM 9/26/13 5:15 PM 625650 26-Sep-13 BREAK2 9/26/13 4:30 PM 9/26/13 4:45 PM 638815 26-Sep-13 BREAK2 9/26/13 4:00 PM 9/26/13 4:15 PM 621649 26-Sep-13 BREAK2 9/26/13 3:30 PM 9/26/13 3:45 PM 567005 26-Sep-13 BREAK2 9/26/13 3:30 PM 9/26/13 3:45 PM 626009 26-Sep-13 LUNCH 9/26/13 2:30 PM 9/26/13 3:30 PM 625650 26-Sep-13 LUNCH 9/26/13 1:30 PM 9/26/13 2:30 PM 638815 26-Sep-13 LUNCH 9/26/13 1:30 PM 9/26/13 2:30 PM 621649 26-Sep-13 LUNCH 9/26/13 12:30 PM 9/26/13 1:30 PM 567005 26-Sep-13 LUNCH 9/26/13 12:30 PM 9/26/13 1:30 PM 626009 26-Sep-13 BREAK1 9/26/13 11:45 AM 9/26/13 12:00 PM 625650 26-Sep-13 BREAK1 9/26/13 11:30 AM 9/26/13 11:45 AM 638815 26-Sep-13 BREAK1 9/26/13 11:45 AM 9/26/13 12:00 PM 621649 26-Sep-13 BREAK1 9/26/13 9:30 AM 9/26/13 9:45 AM 567005 26-Sep-13 BREAK1 9/26/13 9:30 AM 9/26/13 9:45 AM </code></pre> <p><strong>This is the Actual Times</strong></p> <pre><code>EMP_ID Seg_Code Start_Time Stop_Time 625650 Break 9/26/2013 17:54 9/26/2013 17:55 567005 Break 9/26/2013 14:56 9/26/2013 14:59 567005 Break 9/26/2013 15:32 9/26/2013 15:44 638815 Break 9/26/2013 16:34 9/26/2013 16:47 567005 Break 9/26/2013 10:08 9/26/2013 10:21 626009 Break 9/26/2013 17:01 9/26/2013 17:15 625650 Break 9/26/2013 11:31 9/26/2013 11:45 626009 Break 9/26/2013 11:52 9/26/2013 12:07 621649 Break 9/26/2013 9:34 9/26/2013 9:48 621649 Break 9/26/2013 15:31 9/26/2013 15:45 638815 Break 9/26/2013 11:46 9/26/2013 12:02 625650 Break 9/26/2013 16:35 9/26/2013 16:51 567005 Lunch 9/26/2013 12:31 9/26/2013 13:29 625650 Lunch 9/26/2013 13:31 9/26/2013 14:30 626009 Lunch 9/26/2013 14:31 9/26/2013 15:30 638815 Lunch 9/26/2013 13:31 9/26/2013 14:30 621649 Lunch 9/26/2013 12:31 9/26/2013 13:30 </code></pre> <p>I am trying to get the difference (in minutes) between when they are scheduled, and when they are actually taking breaks. A correct example is:</p> <pre><code>Badge Seg_Code Scheduled Start Scheduled Stop Actual Start Actual Stop Difference Seg_Duration 192329 Lunch 9/26/13 8:15 AM 9/26/13 9:15 AM 9/26/2013 8:18:27 AM 9/26/2013 9:17:59 AM 3 0:00:59:32 </code></pre> <p>Thank you again</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload