query which combines 2 two tables with different dates in an query output

Question

Hello, I'm using SQL ser er 2008 R2 Standard

I have 2 tables:  employee_department, and employee_city. 

Employee_department contains:

employee        dept_startdate   department   (so there is a history of deparment changes)

employee_city contains:

employee     city_start_date    city  (so there is a history of city changes)

Example: Suppose I have employee with ID 12345

City history of employee 12345:

employee city_startdate   city

12345      2008-01-01       Boston

12345      2010-01-01       New York

Department history of employee 12345:

employee Dept_startdate    department

12345          2004-12-22         Sales

12345          2006-11-30        Marketing

12345          2010-02-01        Finance

12345          2011-06-01         Marketing

Now I want to make a query to join thes tables, where the desired result should be this:

date                  city             department

2004-12-22      Boston           Sales

2006-11-30      Boston           Marketing

2010-01-01      New York       Marketing   (here you see that city changed to New york from 2010-01-01, when he still worked at marketing)

2010-02-01      New York       Finance

2011-06-01      New York        Marketing

Another problem is that city history is started from 2008-01-01, but department history is older, so I want for those older records to have the first (oldest) city record.

Regards, Hennie

Answer 1

Hi Hennie,

Your question is not very clear. Please explain.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

Are you looking for the below:

Drop table Table1,Table2
create Table Table1(City_sdate date, City varchar(50))
Insert into Table1 Select '2008-01-01','Boston'
Insert into Table1 Select '2010-01-01','NY'
Create Table Table2(Dept_sdate date, dep varchar(50))
Insert into Table2 Select '2004-01-01','sales'
Insert into Table2 Select '2006-01-01','Marketing'
Insert into Table2 Select '2010-02-01','Finance'
Insert into Table2 Select '2011-06-01','Marketing'
Select a.dep,B.* From Table2 A
Cross apply (Select Top 1 * From Table1 B Where B.City_sdate >A.Dept_sdate order by B.City_sdate asc)B
Union All
Select a.dep,B.* From Table2 A
Cross apply (Select Top 1 * From Table1 B Where B.City_sdate <A.Dept_sdate order by B.City_sdate desc)B
Union All
Select B.dep,A.* From Table1 A
Cross apply (Select Top 1 * From Table2 B Where A.City_sdate >B.Dept_sdate order by b.Dept_sdate desc)B

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

I want to buid a query which displays a row for each time the department or city changes.

So in the example there are 4 department records for this employee, and 2 city records. But I want one query which combines it, so if the city changes at some date, but the department doesn't, ther should be a separate  record for this in the query. And if the employee changes department (but not city), the query should show that record as a separate record, which means the desired output is (for the example employee):  

date                  city             department

2004-12-22      Boston           Sales

2006-11-30      Boston           Marketing

2010-01-01      New York       Marketing   (here you see that city changed to New york from 2010-01-01, when he still worked at marketing)

2010-02-01      New York       Finance

2011-06-01      New York        Marketing

Answer 4

Hello Latheesh,

Thank you for your reply, but where does it leave the employees?

Regards, Hennie

Answer 5

ahhaa...I made it simple. If you have employee, please add to the table and add employee in the join. thats it.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 6

Try this:

Drop table Table1,Table2
create Table Table1(Emp int,City_sdate date, City varchar(50))
Insert into Table1 Select 12345,'2008-01-01','Boston'
Insert into Table1 Select 12345,'2010-01-01','NY'
Create Table Table2(Emp int,Dept_sdate date, dep varchar(50))
Insert into Table2 Select 12345,'2004-01-01','sales'
Insert into Table2 Select 12345,'2006-01-01','Marketing'
Insert into Table2 Select 12345,'2010-02-01','Finance'
Insert into Table2 Select 12345,'2011-06-01','Marketing'
Select * From Table2 A
Cross apply (Select Top 1 * From Table1 B Where A.Emp = B.Emp and B.City_sdate >A.Dept_sdate order by B.City_sdate asc)B
Union All
Select * From Table2 A
Cross apply (Select Top 1 * From Table1 B Where A.Emp = B.Emp and B.City_sdate <A.Dept_sdate order by B.City_sdate desc)B
Union All
Select * From Table1 A
Cross apply (Select Top 1 * From Table2 B Where A.Emp = B.Emp and A.City_sdate >B.Dept_sdate order by b.Dept_sdate desc)B
 
 
 

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 7

Try the below only for required column

Drop table Table1,Table2
create Table Table1(Emp int,City_sdate date, City varchar(50))
Insert into Table1 Select 12345,'2008-01-01','Boston'
Insert into Table1 Select 12345,'2010-01-01','NY'
Create Table Table2(Emp int,Dept_sdate date, dep varchar(50))
Insert into Table2 Select 12345,'2004-01-01','sales'
Insert into Table2 Select 12345,'2006-01-01','Marketing'
Insert into Table2 Select 12345,'2010-02-01','Finance'
Insert into Table2 Select 12345,'2011-06-01','Marketing'
Select a.dep,B.Emp,a.Dept_sdate,B.City From Table2 A
Cross apply (Select Top 1 * From Table1 B Where A.Emp = B.Emp and B.City_sdate >A.Dept_sdate order by B.City_sdate asc)B
Union All
Select a.dep,B.Emp,a.Dept_sdate,B.City From Table2 A
Cross apply (Select Top 1 * From Table1 B Where A.Emp = B.Emp and B.City_sdate <A.Dept_sdate order by B.City_sdate desc)B
Union All
Select B.dep,A.* From Table1 A
Cross apply (Select Top 1 * From Table2 B Where A.Emp = B.Emp and A.City_sdate >B.Dept_sdate order by b.Dept_sdate desc)B

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 8

...Almost correct output :-), but 1 row too many: line  2006-11-30      Boston           Marketing  is double....

date                  city             department

2004-12-22      Boston           Sales

2006-11-30      Boston           Marketing

2006-11-30      Boston           Marketing

2010-01-01      New York       Marketing   (here you see that city changed to New york from 2010-01-01, when he still worked at marketing)

2010-02-01      New York       Finance

2011-06-01      New York        Marketing

Answer 9

Check It Out!

Create Table #Employee_department
(
employee        INT,
dept_startdate   DATETIME,
department	VARCHAR(50)
)

Create Table #Employee_city
(
employee     Int,
city_start_date  datetime,  
city varchar(50)
)

Insert #Employee_department values(12345,'2004-12-22','Sales')
Insert #Employee_department values(12345,'2006-11-30','Marketing')
Insert #Employee_department values(12345,'2010-02-01','Finance')
Insert #Employee_department values(12345,'2011-06-01','Marketing')

Insert #Employee_city Values(12345,'2008-01-01','Boston')
Insert #Employee_city Values(12345,'2010-01-01','New York')

;With DeptChange
As
(
Select Distinct D.dept_startdate, D.department from #Employee_department D,#Employee_city C
where (D.employee = C.employee)
),CityChange
As
(
Select Distinct C.city_start_date, c.City from #Employee_department D join #Employee_city C
On (D.employee = C.employee)
)

select * from DeptChange
Union 
select * from CityChange

Drop Table #Employee_department
Drop Table #Employee_city

Answer 10

Try Union Instead of Union All.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 11

...sounds nice, but what if the city date is the same as the department date? Then it should be one record containing both changes..

Regards, Hennie

Answer 12

Got it!!! Yeap, you may alter the statement < and > with (</>)= for this kind of query.

Now I think there should be better way, let me try and let you know.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 13

..Hi Lathees,

Thanks thats works! Maybe you als know how to get 1 date column in the query, like in my previous output example?

 

Regards, Hennie

Answer 14

..Maybe you als know how to get 1 date column in the query, like in my previous output example?

Regards, Hennie

Sorry, I dont get you. Please elaborate..(not native)

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 15

well, the output gives a column for dept_startdate and a column for city_startdate. I wish to have one column, depending on which has changed (city startdate or dept startdate):

date                  city             department

2004-12-22      Boston           Sales

2006-11-30      Boston           Marketing

2010-01-01      New York       Marketing   (here you see that city changed to New york from 2010-01-01, when he still worked at marketing)

2010-02-01      New York       Finance

2011-06-01      New York        Marketing

Here the first 2 dates (2004-12-22 and 2006-11-30)  are department date, but then, 2010-01-01 is a city start date because that date is before the next department startdate (2010-02-01) 2011-06-01 is also al department start date.

Answer 16

Its only the column name right? You can very well change it accordingly.

You may try the below:

Drop table Table1,Table2
create Table Table1(Emp int,City_sdate date, City varchar(50))
Insert into Table1 Select 12345,'2008-01-01','Boston'
Insert into Table1 Select 12345,'2010-01-01','NY'
Create Table Table2(Emp int,Dept_sdate date, dep varchar(50))
Insert into Table2 Select 12345,'2004-01-01','sales'
Insert into Table2 Select 12345,'2006-01-01','Marketing'
Insert into Table2 Select 12345,'2010-02-01','Finance'
Insert into Table2 Select 12345,'2011-06-01','Marketing'
Select a.dep,B.Emp,a.Dept_sdate 'start_date',B.City From Table2 A
Cross apply (Select Top 1 * From Table1 B Where A.Emp = B.Emp and B.City_sdate >A.Dept_sdate order by B.City_sdate asc)B
Union 
Select a.dep,B.Emp,a.Dept_sdate  'start_date',B.City From Table2 A
Cross apply (Select Top 1 * From Table1 B Where A.Emp = B.Emp and B.City_sdate <A.Dept_sdate order by B.City_sdate desc)B
Union 
Select B.dep,A.Emp,a.City_sdate  'start_date',a.City From Table1 A
Cross apply (Select Top 1 * From Table2 B Where A.Emp = B.Emp and A.City_sdate >B.Dept_sdate order by b.Dept_sdate desc)B

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 17

strange, then I get 6 records again, it should be 5..  SO this gives 5 rows (correct):

Select a.dep,B.* From Table2 A
Cross apply (Select Top 1 * From Table1 B Where A.Emp = B.Emp and B.City_sdate >A.Dept_sdate order by B.City_sdate asc)B
Union All
Select a.dep,B.* From Table2 A
Cross apply (Select Top 1 * From Table1 B Where A.Emp = B.Emp and B.City_sdate <A.Dept_sdate order by B.City_sdate desc)B
Union All
Select B.dep,A.* From Table1 A
Cross apply (Select Top 1 * From Table2 B Where A.Emp = B.Emp and  A.City_sdate >B.Dept_sdate order by b.Dept_sdate desc)B

... and this gives 6 rows (not correct):

Select a.dep,B.Emp,a.Dept_sdate 'start_date',B.City From Table2 A
Cross apply (Select Top 1 * From Table1 B Where A.Emp = B.Emp and B.City_sdate >A.Dept_sdate order by B.City_sdate asc)B
Union
Select a.dep,B.Emp,a.Dept_sdate  'start_date',B.City From Table2 A
Cross apply (Select Top 1 * From Table1 B Where A.Emp = B.Emp and B.City_sdate <A.Dept_sdate order by B.City_sdate desc)B
Union
Select B.dep,A.Emp,a.City_sdate  'start_date',a.City From Table1 A
Cross apply (Select Top 1 * From Table2 B Where A.Emp = B.Emp and A.City_sdate >B.Dept_sdate order by b.Dept_sdate desc)B

Answer 18

...Hello Latheesh,

I was thinkign about this matter, and actually you were right, it should be 6 lines instead of 5, so your answer is right!

Many many thanks for helping me,

Regards,

Hennie