CODEWITHSUNDEEP
sqlsql-server
Yajnos
Yajnos

Reputation: 55

Duplicate record after join

I have two tables EmployeeEntryTime and EmployeeExitTime my concern is everytime i join them it returns a duplicate record instead of only 5 records total showing EntryTime and ExitTime and other data.

EmployeeEntryTime table data ff.

EmpID EntryTime Event
8703 2021-03-14 00:00:54.000 random event
8703 2021-03-14 17:16:08.000 random event
8703 2021-03-18 05:03:00.000 random event
8703 2021-03-19 05:13:01.000 random event
8703 2021-03-20 17:12:38.000 random event

EmployeeExitTime table data ff.

EmpID ExitTime Event
8703 2021-03-14 06:07:13.000 random event
8703 2021-03-15 06:11:30.000 random event
8703 2021-03-18 18:06:31.000 random event
8703 2021-03-19 18:06:08.000 random event
8703 2021-03-20 19:59:12.000 random event

The output i need is the ff.

EmpID EntryTime Event ExitTime Event
8703 2021-03-14 00:00:54.000 random event 2021-03-14 06:07:13.000 random event
8703 2021-03-14 17:16:08.000 random event 2021-03-15 06:11:30.000 random event
8703 2021-03-18 05:03:00.000 random event 2021-03-18 18:06:31.000 random event
8703 2021-03-19 05:13:01.000 random event 2021-03-19 18:06:08.000 random event
8703 2021-03-20 17:12:38.000 random event 2021-03-20 19:59:12.000 random event

My query is.

select
    empEntryTime.EmployeeNumber,
    empEntryTime.EntryTime,
    empEntryTime.EntryPoint,
    empExitTime.ExitTime,
    empExitTime.ExitPoint
from
    EmployeeEntryTime empEntryTime
inner join EmployeeExitTime empExitTime 
    on empEntryTime.EmployeeNumber = empExitTime.EmployeeNumber    
order by
    empEntryTime.EntryTime,
    empExitTime.ExitTime

The output i get is below image note: i remove the other columns to avoid getting sued for data privacy.

enter image description here

Upvotes: 0

Views: 41

Answers (2)

Stu
Stu

Reputation: 32614

You just need some additional correlation, an apply makes this straight forward

select et.EmpId, et.EntryTime, et.Event, xt.ExitTime, xt.Event
from EmployeeEntryTime et
cross apply (
    select top (1) ExitTime, Event
    from EmployeeExitTime xt
    where xt.EmpId=et.EmpId and xt.ExitTime>et.EntryTime
    order by ExitTime
)xt
order by et.EntryTime

Upvotes: 1

eshirvana
eshirvana

Reputation: 24613

if exit time is always in the same day , then you need to add another criteria to your current query:

select
    empEntryTime.EmployeeNumber,
    empEntryTime.EntryTime,
    empEntryTime.EntryPoint,
    empExitTime.ExitTime,
    empExitTime.ExitPoint
from
    EmployeeEntryTime empEntryTime
inner join EmployeeExitTime empExitTime 
    on empEntryTime.EmployeeNumber = empExitTime.EmployeeNumber
    and cast(empEntryTime.EntryTime as date) = cast(empExitTime.ExitTime as date)
order by
    empEntryTime.EntryTime,
    empExitTime.ExitTime

Upvotes: 1

Related Questions