divya.trehan573 February 2016

Sql query to fetch minimun date start against each employee

I have two table asg and work rship

In work rship table there is date_start for each employee. For few employee date_start is duplicate so i have to choose the min(date_start) from work rship table

for this i wrote a query :

SELECT assignment_name,
  REGEXP_SUBSTR(ASSIGNMENT_NAME, '[0-9]+') PERSON_NUMBER,
  NVL( wrk.date_start,t.effective_start_date) ,
  NVL( WRK.WORKER_TYPE,'E'),
  NVL( WRK.LEGAL_EMPLOYER_NAME, 'N/A')
FROM
  (SELECT apps.assignment_table.*,
    COUNT(*) OVER (PARTITION BY assignment_name, effective_start_date, effective_end_date, effective_latest_change) AS c
  FROM apps.assignment_table
  ) T
LEFT OUTER JOIN
  (SELECT *
  FROM apps.work_table
  WHERE date_start =
    (SELECT MIN(date_start) FROM apps.work_table
    )
  ) wrk
ON regexp_substr(t.assignment_name, '[0-9]+')=wrk.person_number
WHERE C   =1;

But in the above query i did a mistake of just selecting select min(date_start) from apps.work_table this means min(date_start) from the entire table. Instead of this i should have included the regexp_substr(t.assignment_name, '[0-9]+')=wrk.person_number inside the inline query itself.

But now when i am including it.. its not working. can somebody point out the erroe.

Answers


MT0 February 2016

LEFT OUTER JOIN
  (SELECT *
  FROM apps.work_table
  WHERE date_start =
    (SELECT MIN(date_start) FROM apps.work_table
    )
  ) wrk
ON

Unless the start_date for the person is the minimum value across all people in the entire table (and not just the minimum date for this person) then the code above will not find any rows that will match the person you want.

What you probably meant to do is something like:

LEFT OUTER JOIN
( SELECT *
  FROM   (
    SELECT t.*,
           ROW_NUMBER() OVER ( PARTITION BY person_number ORDER BY date_start ASC ) AS rn
    FROM   apps.work_table t
  )
  WHERE  rn = 1
) wrk
ON

Post Status

Asked in February 2016
Viewed 3,474 times
Voted 11
Answered 1 times

Search




Leave an answer