We help IT Professionals succeed at work.

get the last vacation record only for all employees

Mohammad Alsolaiman
on
58 Views
Last Modified: 2018-09-11
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

EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
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
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
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