Get max and min date corresponding columns in sql server

Transact-SQL
, ,
1

I have two tables.

Emp:
create table emp(id ind,deptno int, sdate date,edate date,locid int)
and date look like below

id | Deptno | sdate | edate | locid
1 | 10 |2014-09-19 |2009-09-25 | 100
1 | 10 |2014-11-06 |2009-11-06 | 100
1 | 10 |2014-12-11 |2014-12-14 | 100
1 | 10 |2009-07-01 |2009-07-20 | 100
2 | 20 |2007-07-11 |2007-07-17 | 104
Empl1:
create table emp1(id ind,deptno int, sdate date,edate date,locid int)
and data look like below

Id | Deptno | edate | refid | refid1
1 | 10 | 2014-11-06 | 201 | 204
1 | 10 | 2014-12-13 | 204 | 202
1 | 10 | 2014-12-12 | 207 | 124
1 | 10 | 2003-05-04 | 105 | 103
2 | 20 | 2007-07-14 | 102 | 106
I am trying to combine these two tables into the output below:

Id | Deptno | locid | current_refid | current_refid1 | last_refid | last_refid1
1 | 10 | 101 | 201 | 204 | 204 |202
2 | 20 | 104 | 102 | 106 | 102 |106
The tables share the common columns id and deptno. To get the above result, the emp1.edate must be between emp.sdate and emp.edate.

If that condition is met, we need to retrieve the refid and refid1 values corresponding to the lowest edate as last_refid and last_refid1. Then we need the refid and refid1 values corresponding to the emp1.edate between emp.stdate and emp.edate to be retrieved as the current_refid and current_refid1.

I tried to do this using the following query, but it does not give the expected result:

select a.id,a.deptno,a.locid,b.refid,b.refid1
from
emp b

join
(
select * ,row_number()over (partition by id ,deptno order by edate )as rn
from emp1
) a
where b.edate<=a.sdate and b.edate<=a.edate
please tell me query how to achive this task in sql server

2

Hi

does this help you ?

SELECT a.*
FROM #emp1 a
INNER JOIN #emp b ON a.id = b.id
	AND a.deptno = b.deptno
WHERE a.edate BETWEEN b.sdate
		AND b.edate

-- Heres the data script i used

/*

drop table #emp
create table #emp(id int,deptno int, sdate date,edate date,locid int)
insert into #emp select 1 , 10 ,'2008-09-19','2009-09-25', 100
insert into #emp select 1 , 10 ,'2007-11-06','2009-11-06', 100
insert into #emp select 1 , 10 ,'2014-12-11','2014-12-14', 100
insert into #emp select 1 , 10 ,'2009-07-01','2009-07-20', 100
insert into #emp select 2 , 20 ,'2007-07-11','2007-07-17', 104

drop table #emp1
create table #emp1(id int,deptno int, edate date,locid int)
insert into #emp1 select 1 , 10 ,'2014-11-06', 201
insert into #emp1 select 1 , 10 ,'2014-12-13', 204
insert into #emp1 select 1 , 10 ,'2014-12-12', 207
insert into #emp1 select 1 , 10 ,'2003-05-04', 105
insert into #emp1 select 2 , 20 ,'2007-07-14', 102

select * from #emp
select * from #emp1

*/