get the last vacation record only for all employees

How could I get the last vacation record only for all employees?
Table name : TBPRSVAC
Columns: vac_emp_no, vac_vac_type, vac_start_date, vac_end_date, vac_spend_place
Mohammad Alsolaimanapplication programmerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
You can use row_number() over() to arrive at a "most recent" row per employee, like this:
SELECT
    vac_emp_no
  , vac_vac_type
  , vac_start_date
  , vac_end_date
  , vac_spend_place
FROM (
    SELECT
        vac_emp_no
      , vac_vac_type
      , vac_start_date
      , vac_end_date
      , vac_spend_place
      , ROW_NUMBER() OVER (PARTITION BY vac_emp_no ORDER BY vac_end_date DESC) AS rn
    FROM TBPRSVAC
    ) vac
WHERE rn = 1

Open in new window


But depending on context, AND database, you might find that using an apply operator works for this, e.g.

SELECT
    emp.*
  , vac.vac_vac_type
  , vac.vac_start_date
  , vac.vac_end_date
  , vac.vac_spend_place
FROM employee emp
OUTER APPLY (
    SELECT TOP (1)
        vac_vac_type
      , vac_start_date
      , vac_end_date
      , vac_spend_place
    FROM TBPRSVAC vac
    WHERE emp.emp_no = vac.vac_emp_no
    ORDER BY
        vac_end_date DESC
    ) vac

Open in new window


Notes:
you may also want to use [vac_start_date] in the ordering of either row_number() or within the apply
outer apply is used in case there is no vacation record (it acts like a left join)
the queries above both work in SQL Server. The first query will work in any db supporting row_number()

NBNB: please nominate which database you actually use because "SQL" by itself doesn't narrow down the choices enough.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
Or you might try:
SELECT TBPRSVAC.* 
FROM TBPRSVAC
INNER JOIN (
SELECT vac_emp_no, Max(vac_Start_Date) as MaxStart
FROM TBPRSVAC
Group by vac_Emp_No
) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.vac_Start_Date

Open in new window

I know this will work to return the recordset, but it may not be updateable, depending on the database you are using.
0
Mohammad Alsolaimanapplication programmerAuthor Commented:
PortletPaul
I had try both solutions, and they were awesome .

Dale Fye
I had encountered this error
Invalid column name 'vac_Start_Date'.
It was pointing to line Number 7
) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.vac_Start_Date
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Enroll Today!
Dale FyeCommented:
That should have been:

) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.MaxStart
0
Mohammad Alsolaimanapplication programmerAuthor Commented:
Dale Fye
It works now, but there is a lot of repeat. I mean it bring the last vacation for each employee, and repeat it several times.
0
Mohammad Alsolaimanapplication programmerAuthor Commented:
thanks to both of u
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.