get the last vacation record only for all employees

Mohammad Alsolaiman
Mohammad Alsolaiman used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
Mohammad Alsolaimanapplication programmer

Author

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
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
That should have been:

) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.MaxStart
Mohammad Alsolaimanapplication programmer

Author

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.
Mohammad Alsolaimanapplication programmer

Author

Commented:
thanks to both of u

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial