CODEWITHSUNDEEP
sqlsql-server
Andreas
Andreas

Reputation: 25

Need to select latest registration by department when joining with person

Person:

Id  Name
1   Anton
2   Ib
3   Knud
4   Hans

Registration:

Id  PersonId    Status  DateTime        Department
5   1           1       11-1-2013 10:00 1
6   1           0       10-1-2013 09:00 1
7   1           2       10-1-2013 14:05 1
8   1           2       09-1-2013 09:00 1
9   2           2       09-1-2013 09:00 2
10  3           2       09-1-2013 09:00 3
11  4           2       10-1-2013 17:00 1

I need to select the latest registration(by person) for the day (10-1-2013) for department 1

Expected output:

Name    R.Id    P.Id    Status  DateTime        Department

Anton   7       1       2       10-1-2013 14:05 1
Hans    11      4       2       10-1-2013 17:00 1

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

I think you just want this:

select top 1 p.name, r.id, p.id, r.status, r.datetime, r.department
from registration r join
     person p
     on r.personid = p.personid
where department = 1 and cast(daTetime as date) = '10-1-2013'
order by datetime desc

The latest registration by department, not by person. This just uses a where clause to filter by day and department, orders by the datetime column and chooses the first one.

To get all of them:

select p.name, r.id, p.id, r.status, r.datetime, r.department
from (select r.*, row_number() over (partition by person order by datetime desc) as seqnum
      from registration r
      where department = 1 and cast(daTetime as date) = '10-1-2013'
     ) r join
     person p
     on r.personid = p.personid
where seqnum = 1
order by datetime desc

Upvotes: 2

Related Questions