Asad Asad - 1 month ago 7
SQL Question

I want to display Position Name as "New Position" and "Old Position" in two columns but in one row with respect to their creation date in Oracle HRMS

select *
from (
select distinct
papf.employee_number
, papf.full_name/*, pap.name*/
, pg.name "Grade"
, MAX(pap.creation_date) AS "Maximum Date"
, MIN(pap.creation_date) AS "Minimum Date"
, CASE WHEN max(pap.creation_date) = (pap.creation_date)
THEN pap.name
ELSE null END AS "New Position"
, CASE WHEN min(pap.creation_date) = (pap.creation_date)
THEN pap.name
ELSE null END AS "Old Position"
from per_all_positions pap
, per_all_assignments_f paaf
, per_all_people_f papf
, per_grades pg
where paaf.position_id = pap.position_id
and paaf.person_id = papf.person_id
and paaf.grade_id = pg.grade_id
and papf.employee_number = 00871
and papf.effective_end_date = '31-DEC-4712'
and pg.name IN ('SVP', 'EVP I', 'EVP II', 'SEVP I', 'SEVP II')
--and paaf.effective_end_date = '31-DEC-4712'
group by papf.employee_number
, papf.full_name
, pap.name
, pg.name
, pap.creation_date
)



EMPLOYEE_NUMBER FULL_NAME Grade Maximum Date Minimum Date New Position Old Position
1 00871 Mr. Tahir Mahmood Afzal Raja SVP 4/9/2014 3:44:22 PM 4/9/2014 3:44:22 PM Area Manager.1.MBL - Area Office Islamabad II Area Manager.1.MBL - Area Office Islamabad II
2 00871 Mr. Tahir Mahmood Afzal Raja SVP 7/24/2013 9:57:35 AM 7/24/2013 9:57:35 AM Branch Manager.1.MBL - Bahria Town Branch, Rawalpindi Branch Manager.1.MBL - Bahria Town Branch, Rawalpindi



As you can see the output it shows new and old positions in two rows
and repeating it, so please help me how to do what I want ? This query
is of oracle HRMS and written in PL/SQL developer.

Answer

You query are good but you forget wrap CASE ... with max and delete grouping by pap.creation_date. If you do it you will get what you need. But I d like to use aggregate function MAX() keep (first). I write code with it

select * 
  from (
        select distinct 
              papf.employee_number
            , papf.full_name/*, pap.name*/
            , pg.name "Grade"
            , MAX(pap.creation_date) AS "Maximum Date"
            , MIN(pap.creation_date) AS "Minimum Date"
            , MAX(pap.name) KEEP (dense_rank FIRST order by pap.creation_date DESC) AS "New Position"
            , MIN(pap.name) KEEP (dense_rank FIRST order by pap.creation_date) AS "Old Position"
          from per_all_positions pap
             , per_all_assignments_f paaf
             , per_all_people_f papf
             , per_grades pg
          where paaf.position_id = pap.position_id
          and paaf.person_id = papf.person_id
          and paaf.grade_id = pg.grade_id
          and papf.employee_number = 00871
          and papf.effective_end_date = '31-DEC-4712'
          and pg.name IN ('SVP', 'EVP I', 'EVP II', 'SEVP I', 'SEVP II')
          --and paaf.effective_end_date = '31-DEC-4712'
          group by papf.employee_number 
                 , papf.full_name
                 , pg.name
       )