| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-10-10, 12:37
|
|
Registered User
|
|
Join Date: Nov 2009
Posts: 18
|
|
|
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.
|
|
02-10-10, 12:55
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Use FULL join instead of INNER
Quote:
Originally Posted by Howardw
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
|
|
02-10-10, 13:31
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
|
|
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 22:28.
Reason: Add WHERE clause. Add statement "You are using DB2 9.5. It will support ...".
|
02-11-10, 06:35
|
|
Registered User
|
|
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 555
|
|
Quote:
Originally Posted by tonkuma
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 
|
|
02-12-10, 03:18
|
|
Registered User
|
|
Join Date: Nov 2009
Posts: 18
|
|
|
Thanks - Tonkuma
This answered my question very accurately
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
How to create one row from a multiple of tables - driven by an event date
