Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Here is some sample data:

This is the Scheduled Times:

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

This is the Actual Times

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

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:

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

Thank you

share|improve this question

1 Answer 1

EDIT with updated code:

Try something like this. The idea is to order actual breaks by time and then append the cardinal row_number to 'BREAK' so that it joins wtih BREAK1, BREAK2, etc.

CREATE TABLE #Scheduled
(Emp_ID int, NOM_DATE DATE,  SEG_CODE varchar(10),    START_MOMENT Datetime,   STOP_MOMENT Datetime);

INSERT INTO #Scheduled VALUES(626009  ,'26-Sep-13',   'BREAK2',          '9/26/13 5:00 PM', '9/26/13 5:15 PM');
INSERT INTO #Scheduled VALUES(625650  ,'26-Sep-13',   'BREAK2',          '9/26/13 4:30 PM', '9/26/13 4:45 PM');
INSERT INTO #Scheduled VALUES(638815  ,'26-Sep-13',   'BREAK2',          '9/26/13 4:00 PM', '9/26/13 4:15 PM');
INSERT INTO #Scheduled VALUES(621649  ,'26-Sep-13',   'BREAK2',          '9/26/13 3:30 PM', '9/26/13 3:45 PM');
INSERT INTO #Scheduled VALUES(567005  ,'26-Sep-13',   'BREAK2',          '9/26/13 3:30 PM', '9/26/13 3:45 PM');
INSERT INTO #Scheduled VALUES(626009  ,'26-Sep-13',   'LUNCH ',          '9/26/13 2:30 PM', '9/26/13 3:30 PM');
INSERT INTO #Scheduled VALUES(625650  ,'26-Sep-13',   'LUNCH ',          '9/26/13 1:30 PM', '9/26/13 2:30 PM');
INSERT INTO #Scheduled VALUES(638815  ,'26-Sep-13',   'LUNCH ',          '9/26/13 1:30 PM', '9/26/13 2:30 PM');
INSERT INTO #Scheduled VALUES(621649  ,'26-Sep-13',   'LUNCH ',         '9/26/13 12:30 PM', '9/26/13 1:30 PM');
INSERT INTO #Scheduled VALUES(567005  ,'26-Sep-13',   'LUNCH ',         '9/26/13 12:30 PM', '9/26/13 1:30 PM');
INSERT INTO #Scheduled VALUES(626009  ,'26-Sep-13',   'BREAK1',         '9/26/13 11:45 AM', '9/26/13 12:00 PM');
INSERT INTO #Scheduled VALUES(625650  ,'26-Sep-13',   'BREAK1',         '9/26/13 11:30 AM', '9/26/13 11:45 AM');
INSERT INTO #Scheduled VALUES(638815  ,'26-Sep-13',   'BREAK1',         '9/26/13 11:45 AM', '9/26/13 12:00 PM');
INSERT INTO #Scheduled VALUES(621649  ,'26-Sep-13',   'BREAK1',         '9/26/13 9:30 AM',  '9/26/13 9:45 AM');
INSERT INTO #Scheduled VALUES(567005  ,'26-Sep-13',   'BREAK1',         '9/26/13 9:30 AM',  '9/26/13 9:45 AM');

CREATE TABLE #Actual
(Emp_ID int, SEG_CODE varchar(10),    Start_Time Datetime,   Stop_Time Datetime);

INSERT INTO #Actual VALUES(625650,  'Break',           '9/26/2013 17:54','9/26/2013 17:55');
INSERT INTO #Actual VALUES(567005,  'Break',           '9/26/2013 14:56','9/26/2013 14:59');
INSERT INTO #Actual VALUES(567005,  'Break',           '9/26/2013 15:32','9/26/2013 15:44');
INSERT INTO #Actual VALUES(638815,  'Break',           '9/26/2013 16:34','9/26/2013 16:47');
INSERT INTO #Actual VALUES(567005,  'Break',           '9/26/2013 10:08','9/26/2013 10:21');
INSERT INTO #Actual VALUES(626009,  'Break',           '9/26/2013 17:01','9/26/2013 17:15');
INSERT INTO #Actual VALUES(625650,  'Break',           '9/26/2013 11:31','9/26/2013 11:45');
INSERT INTO #Actual VALUES(626009,  'Break',           '9/26/2013 11:52','9/26/2013 12:07');
INSERT INTO #Actual VALUES(621649,  'Break',           '9/26/2013  9:34','9/26/2013  9:48');
INSERT INTO #Actual VALUES(621649,  'Break',           '9/26/2013 15:31','9/26/2013 15:45');
INSERT INTO #Actual VALUES(638815,  'Break',           '9/26/2013 11:46','9/26/2013 12:02');
INSERT INTO #Actual VALUES(625650,  'Break',           '9/26/2013 16:35','9/26/2013 16:51');
INSERT INTO #Actual VALUES(567005,  'Lunch',           '9/26/2013 12:31','9/26/2013 13:29');
INSERT INTO #Actual VALUES(625650,  'Lunch',           '9/26/2013 13:31','9/26/2013 14:30');
INSERT INTO #Actual VALUES(626009,  'Lunch',           '9/26/2013 14:31','9/26/2013 15:30');
INSERT INTO #Actual VALUES(638815,  'Lunch',           '9/26/2013 13:31','9/26/2013 14:30');
INSERT INTO #Actual VALUES(621649,  'Lunch',           '9/26/2013 12:31','9/26/2013 13:30');

Then here is some code to get the answer. I handled (after a fashion) unscheduled breaks as well. See:

   SELECT COALESCE(s.Emp_ID,a.EMP_ID) AS 'Badge', COALESCE(s.Seg_Code, a.CalcSeg, 'Unscheduled') AS Seg_Code,
           s.START_MOMENT AS 'Schedule Start', s.STOP_MOMENT AS 'Scheduled Stop',
           a.Start_Time AS 'Actual Start', a.Stop_Time AS 'Actual Stop'
    FROM #Scheduled s
        FULL OUTER JOIN 
             (SELECT Emp_ID,
               CASE
                 WHEN Seg_Code = 'Break' THEN Seg_Code + 
                     CAST(ROW_NUMBER() OVER (PARTITION BY Emp_ID, Seg_Code ORDER BY Start_Time) as CHAR(2))
                 ELSE Seg_Code 
               END AS CalcSeg,
               Start_Time, Stop_Time
              FROM #Actual) AS a
        ON s.EMP_ID = a.Emp_Id
           AND s.Seg_Code = a.CalcSeg
           AND s.NOM_DATE = CAST(a.Start_Time AS DATE)
        ORDER BY Badge, COALESCE(s.Start_Moment, a.Start_Time), SEG_CODE;

    DROP TABLE #Scheduled;
    DROP TABLE #Actual;

That seems to be what you were after.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.