Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    20

    Unanswered: How to create one row from a multiple of tables - driven by an event date

    DB2 9.5 on vista
    I have 3 tables
    Location table
    Emp_id , Location, Date Start, date end
    Job table
    Emp_id , Job, Date Start, date end
    Hours table
    Emp_id , Hours, Date Start, date end

    Each table can have many entries for an employee.
    Location table
    2034,E40,2004-03-04,2008-01-21
    2034,E50,2008-01-22,9999-12-31
    Job table
    2034,B5,2004-03-04,2006-05-02
    2034,B6,2006-05-03,2007-06-12
    2034,C7,2007-06-13,9999-12-31
    Hours table
    2034,3750,2004-03-04,2005-06-11
    2034,3000,2005-06-12,2007-12-31
    2034,3500,2008-01-01,9999-12-31

    I need to create a combined record for each unique occurance of the three events.
    I have a table defined thus
    Emp_id, Location, Job, Hours, date_start, date_end
    The above data in the tables would create these records
    2034,E40,B5,3750,2004-03-04,2005-06-11
    2034,E40,B5,3000,2005-06-12,2006-05-02
    2034,E40,B6,3000,2006-05-03,2007-06-12
    2034,E40,C7,3000,2007-06-13,2007-12-31
    2034,E40,C7,3500,2008-01-01,2008-01-21
    2034,E50,C7,3500,2008-01-22,9999-12-31
    If i can get the start date correct, i can create the end date for each entry from the previous entry in an update query.

    I can write a program to do this process, but can i write an SQL query.

    My attempts have been to use sub-selects and joins. Attached is an effort that was not correct.

    Select t1.employee_number as emp_no
    ,t1.date_start as loc_dte_comm
    ,t1.date_end as loc_dte_csed
    ,t1.location as location
    ,t3.job as job
    ,t2.hours as hours
    ,t2.date_start as dte_start_hrs
    ,t3.date_start as dte_start_job

    from (
    SELECT employee_number
    ,date_start
    ,date_end
    ,location
    from stage1.location loc
    where loc.employee_number = 2034
    ) t1
    inner join stage1.hours t2
    on t1.employee_number = t2.employee_number
    and t1.date_end between t2.date_start
    and t2.date_end
    inner join stage1.job t3
    on t1.employee_number = t3.employee_number
    and t2.employee_number = t3.employee_number
    and t1.date_end between t3.date_start
    and t3.date_end

    order by t1.employee_number, t1.date_start desc

    Any suggestions much appreciated.

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Use FULL join instead of INNER

    Quote Originally Posted by Howardw View Post
    DB2 9.5 on vista
    I have 3 tables
    Location table
    Emp_id , Location, Date Start, date end
    Job table
    Emp_id , Job, Date Start, date end
    Hours table
    Emp_id , Hours, Date Start, date end

    Each table can have many entries for an employee.
    Location table
    2034,E40,2004-03-04,2008-01-21
    2034,E50,2008-01-22,9999-12-31
    Job table
    2034,B5,2004-03-04,2006-05-02
    2034,B6,2006-05-03,2007-06-12
    2034,C7,2007-06-13,9999-12-31
    Hours table
    2034,3750,2004-03-04,2005-06-11
    2034,3000,2005-06-12,2007-12-31
    2034,3500,2008-01-01,9999-12-31

    I need to create a combined record for each unique occurance of the three events.
    I have a table defined thus
    Emp_id, Location, Job, Hours, date_start, date_end
    The above data in the tables would create these records
    2034,E40,B5,3750,2004-03-04,2005-06-11
    2034,E40,B5,3000,2005-06-12,2006-05-02
    2034,E40,B6,3000,2006-05-03,2007-06-12
    2034,E40,C7,3000,2007-06-13,2007-12-31
    2034,E40,C7,3500,2008-01-01,2008-01-21
    2034,E50,C7,3500,2008-01-22,9999-12-31
    If i can get the start date correct, i can create the end date for each entry from the previous entry in an update query.

    I can write a program to do this process, but can i write an SQL query.

    My attempts have been to use sub-selects and joins. Attached is an effort that was not correct.

    Select t1.employee_number as emp_no
    ,t1.date_start as loc_dte_comm
    ,t1.date_end as loc_dte_csed
    ,t1.location as location
    ,t3.job as job
    ,t2.hours as hours
    ,t2.date_start as dte_start_hrs
    ,t3.date_start as dte_start_job

    from (
    SELECT employee_number
    ,date_start
    ,date_end
    ,location
    from stage1.location loc
    where loc.employee_number = 2034
    ) t1
    inner join stage1.hours t2
    on t1.employee_number = t2.employee_number
    and t1.date_end between t2.date_start
    and t2.date_end
    inner join stage1.job t3
    on t1.employee_number = t3.employee_number
    and t2.employee_number = t3.employee_number
    and t1.date_end between t3.date_start
    and t3.date_end

    order by t1.employee_number, t1.date_start desc

    Any suggestions much appreciated.
    You have to use FULL join instead of INNER join...

    Lenny

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example:

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    Location(Emp_id , Location, Date_Start, date_end) AS ( 
    VALUES
      ('2034', 'E40', '2004-03-04', '2008-01-21')
    , ('2034', 'E50', '2008-01-22', '9999-12-31')
    )
    ,Job(Emp_id , Job, Date_Start, date_end) AS (
    VALUES
      ('2034', 'B5', '2004-03-04', '2006-05-02')
    , ('2034', 'B6', '2006-05-03', '2007-06-12')
    , ('2034', 'C7', '2007-06-13', '9999-12-31')
    )
    ,Hours(Emp_id , Hours, Date_Start, date_end) AS (
    VALUES
      ('2034', 3750, '2004-03-04', '2005-06-11')
    , ('2034', 3000, '2005-06-12', '2007-12-31')
    , ('2034', 3500, '2008-01-01', '9999-12-31')
    )
    SELECT t1.Emp_id
         , Location , Job , Hours
         , MAX(t1.Date_Start , t2.Date_Start , t3.Date_Start) Date_Start
         , MIN(t1.date_end   , t2.date_end   , t3.date_end  ) date_end
      FROM Location t1
      JOIN Job      t2
       ON  t2.Emp_id     =  t1.Emp_id
       AND t2.Date_Start <= t1.date_end
       AND t2.date_end   >= t1.Date_Start
      JOIN Hours    t3
       ON  t3.Emp_id     =  t1.Emp_id
       AND t3.Date_Start <= MIN(t1.date_end   , t2.date_end  )
       AND t3.date_end   >= MAX(t1.Date_Start , t2.Date_Start)
     WHERE t1.Emp_id = '2034'
     ORDER BY
           t1.Emp_id , Date_Start
    ;
    ------------------------------------------------------------------------------
    
    EMP_ID LOCATION JOB HOURS       DATE_START DATE_END  
    ------ -------- --- ----------- ---------- ----------
    2034   E40      B5         3750 2004-03-04 2005-06-11
    2034   E40      B5         3000 2005-06-12 2006-05-02
    2034   E40      B6         3000 2006-05-03 2007-06-12
    2034   E40      C7         3000 2007-06-13 2007-12-31
    2034   E40      C7         3500 2008-01-01 2008-01-21
    2034   E50      C7         3500 2008-01-22 9999-12-31
    
      6 record(s) selected.
    If your DB2 version does not support MAX/MIN scalar functions, use CASE expression.

    You are using DB2 9.5. It will support MAX/MIN scalar functions.
    Last edited by tonkuma; 02-10-10 at 23:28. Reason: Add WHERE clause. Add statement "You are using DB2 9.5. It will support ...".

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by tonkuma View Post
    Here is an example:
    You are a SQL-NINJA!

    I do not know much about japanes culture, but I understand that it is better for your health to have a ninja as friend instead of an enemey.
    I am glad, my friend, that you are helping us here

  5. #5
    Join Date
    Nov 2009
    Posts
    20

    Thanks - Tonkuma

    This answered my question very accurately

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •