This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.
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
Table name : TBPRSVAC
Columns: vac_emp_no, vac_vac_type, vac_start_date, vac_end_date, vac_spend_place
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use row_number() over() to arrive at a "most recent" row per employee, like this:
But depending on context, AND database, you might find that using an apply operator works for this, e.g.
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.
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
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
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.
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
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
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
That should have been:
) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.MaxStart
) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.MaxStart
Premium Content
You need an Expert Office subscription to comment.Start Free Trial