Avatar of Mohammad Alsolaiman
Mohammad Alsolaiman
Flag for Saudi Arabia asked on

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
SQL

Avatar of undefined
Last Comment
Mohammad Alsolaiman

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dale Fye

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 Alsolaiman

ASKER
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 Fye

That should have been:

) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.MaxStart
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Mohammad Alsolaiman

ASKER
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 Alsolaiman

ASKER
thanks to both of u