Sql query to find the latest start date when the code was tagged to the employee

Question

I have an assignment table with the union code details as follows -

ASG

NUMBER      START_DATE              END_DATE                POSITION            UNION CODE        MANAGER
10          01-JAN-2006             17-AUG-2017             MANAGER             6790             Cyndy
10          18-AUG-2017             10-JUL-2018             SUPER MANAGER       6790             Cyndy
10          11-JUL-2018             31-12-4712              SUPER MANAGER       6791             Cyndy

10          01-JAN-2006             07-AUG-2017             associate           6790             Bali
10          08-AUG-2017             10-JUL-2019             sr. associate       6790             Bali
10          11-JUL-2019             31-12-4712              MANAGER             6790             Bali

I tweak the below query such that I can find the first date when the latest union code was tagged to the employee. So the output should look like -

NUMBER      START_DATE              POSITION            UNION CODE        MANAGER
10          11-JUL-2018             SUPER MANAGER       6791             Cyndy
10          01-JAN-2006             associate           6790             Bali

I am using the below query to find the latest row but I need the first date when the union code was tagged-

select NUMBER, START_dATE,POSITION,UNION_CODE,MANAGER from 
asg
where sysdate between start_Date and end_date

Why is your output two rows? Assuming that `NUMBER` is the primary key for an employee then you only have a single employee as all the rows have the value `10`. — MT0, Mar 24 '23 at 18:47

MT0 · Accepted Answer · 2023-03-24T18:44:21.727

To get the latest union_code and then the earliest start_date for each number you can use the ROW_NUMBER analytic function:

SELECT *
FROM   (
  SELECT a.*,
         ROW_NUMBER() OVER (
           PARTITION BY "NUMBER" ORDER BY union_code DESC, start_date ASC
         ) AS rn
  FROM   asg a
)
WHERE  rn = 1;

Which, for the sample data:

CREATE TABLE ASG ("NUMBER", START_DATE, END_DATE, POSITION, UNION_CODE, MANAGER) AS
SELECT 10, DATE '2006-01-01', DATE '2017-08-17', 'MANAGER',       6790, 'Cyndy' FROM DUAL UNION ALL
SELECT 10, DATE '2017-08-18', DATE '2018-07-10', 'SUPER MANAGER', 6790, 'Cyndy' FROM DUAL UNION ALL
SELECT 10, DATE '2018-07-11', DATE '4712-12-31', 'SUPER MANAGER', 6791, 'Cyndy' FROM DUAL UNION ALL
SELECT 11, DATE '2006-01-01', DATE '2017-08-07', 'associate',     6790, 'Bali'  FROM DUAL UNION ALL
SELECT 11, DATE '2017-08-08', DATE '2019-07-10', 'sr. associate', 6790, 'Bali'  FROM DUAL UNION ALL
SELECT 11, DATE '2019-07-11', DATE '4712-12-31', 'MANAGER',       6790, 'Bali'  FROM DUAL;

(Assuming that you have 2 employees and they should have different NUMBERs)

Outputs:

NUMBER	START_DATE	END_DATE	POSITION	UNION_CODE	MANAGER	RN
10	2018-07-11 00:00:00	4712-12-31 00:00:00	SUPER MANAGER	6791	Cyndy	1
11	2006-01-01 00:00:00	2017-08-07 00:00:00	associate	6790	Bali	1

fiddle

score 0 · Answer 2 · answered Mar 24 '23 at 16:36

0

Supposing NUMBER to be the employee id:

SELECT *
FROM ASG A1
WHERE UNION_CODE = (SELECT MAX(UNION_CODE) FROM ASG A2 WHERE A1.NUMBER = A2.NUMBER)
AND START_DATE = (SELECT MIN(START_DATE) FROM ASG A3 WHERE A1.NUMBER = A3.NUMBER)

answered Mar 24 '23 at 16:36

lpacheco

976
1
14
27

This does not return the expected output [fiddle](https://dbfiddle.uk/0YKW23Jc) because you are not getting minimum start date for the maximum union code; instead you are separately getting the maximum union code and the minimum start date and there is no correlation. – MT0 Mar 24 '23 at 18:52

Sql query to find the latest start date when the code was tagged to the employee

2 Answers2