Find the data of one column of a table that is larger than the data of one column of another table-CodePudding

I have a table that shows the employees' leave date. I also have another table that shows the date of arrival of employees to the main warehouse of the office. Considering that employees should not enter the main warehouse during the days off, How to find out which employee entered the main warehouse when he went on leave.

LeaveTable that shows Employee leave date table looks like this:

id	EmpName	EmpFamily	EmpFatherName	LeaveDate
1	karim	mansoory	ali	1400/01/02
2	majid	ghasemi	hasan	1400/02/03
3	hasan	hamidi	bagher	1400/02/10
4	hasan	hamidi	bagher	1400/03/15
5	majid	ghasemi	hasan	1400/03/15
6	yaser	akbary	ahmad	1400/03/17
7	hosssein	mohammadi	jasem	1400/03/18
8	yaser	akbary	ahmad	1400/04/02
9	karim	mansoory	ali	1400/04/11
10	karim	mansoory	ali	1400/04/18
11	yaser	akbary	ahmad	1400/05/02

EnterTable Table showing the date of arrival of employees to the main warehouse of the office looks like this:

id	EmpName	EmpFamily	EmpFatherName	EnterDate
1	farzane	ahmadi	jafar	1400/01/02
2	majid	ghasemi	hasan	1400/02/04
3	nima	alimi	saber	1400/02/10
4	akram	gheibi	ahmad	1400/03/17
5	hosssein	mohammadi	jasem	1400/03/19
6	kabir	sabry	abed	1400/04/10
7	yaser	akbary	ahmad	1400/01/07

I want this result

id	EmpName	EmpFamily	EmpFatherName	LeaveDate	EnterDate
1	majid	ghasemi	hasan	1400/02/03	1400/02/04
2	hosssein	mohammadi	jasem	1400/03/18	1400/03/19
3	yaser	akbary	ahmad	1400/05/02	1400/01/07

My SQL code is:

  Select
      LeaveTable.EmpName,
      LeaveTable.EmpFamily,
      LeaveTable.EmpFatherName,
      LeaveTable.LeaveDate ,
      EnterTable.EnterDate 
  from
        LeaveTable
        left outer join EnterTable
            on LeaveTable.EmpName=EnterTable.EmpName and
               LeaveTable.EmpFamily=EnterTable.EmpFamily and
               LeaveTable.EmpFatherName=EnterTable.EmpFatherName
            where EnterTable.EnterDate>LeaveTable.LeaveDate
    order by 
        EmpName,EmpFamily,EmpFatherName,EnterTable.EnterDate,LeaveTable.LeaveDate

But above codes has error. Can every body help me?

CodePudding user response：

I think this is what you want:

select ROW_NUMBER() OVER (ORDER BY min(e.id)), 
  e.EmpName, e.EmpFamily, e.EmpFatherName, min(l.LeaveDate) LeaveDate, min(e.EnterDate) EnterDate
from EnterTable e inner join LeaveTable l
on e.EmpName = l.EmpName and e.EmpFamily = l.EmpFamily and e.EmpFatherName = l.EmpFatherName
group by e.EmpName, e.EmpFamily, e.EmpFatherName

But you have to choose between MIN and MAX in LeaveDate. In Majid you chose the MIN one and in Yaser the MAX one. Why? I chose the MIN one for both, if it is not the right election, please, give me more data and I update the answer.

DBFiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=639d9586886d343fbd53f25e77228046

CodePudding user response：

Select LeaveTable.* ,EnterTable.EnterDate from LeaveTable INNER join EnterTable on LeaveTable.EmpName=EnterTable.EmpName and LeaveTable.EmpFamily=EnterTable.EmpFamily and LeaveTable.EmpFatherName=EnterTable.EmpFatherName where EnterTable.EnterDate>LeaveTable.LeaveDate