The Ouput should only have Emp_Category = 'Permanent' but joining date must be min(joining_date) irrespective of the Emp_Category

Source data

Expected Output

CREATE TABLE TMDL_STANDARD.Emp_his (
  
  Emp_ID            NUMBER,
  Emp_Name          VARCHAR,
  Emp_category      VARCHAR,
  Joining_date      TIMESTAMP_LTZ,
  Emp_Location      VARCHAR
  );

insert into TMDL_STANDARD.Emp_his (Emp_ID, Emp_Name, Emp_category, 
                                   Joining_date, Emp_Location)
values 
    ( 1, 'Jas', 'Permanent', '02/03/2022', 'US'),
    ( 1, 'Jas', 'Temporary', '01/01/2022', 'Ireland'),
    ( 1, 'Jas', 'Temporary', '12/31/2021', 'India'),
    ( 2, 'Tom', 'Permanent', '02/04/2022', 'India'),
    ( 2, 'Tom', 'Permanent', '02/03/2022', 'Uk' ),
    ( 2, 'Tom', 'Permanent', '01/12/2022', 'US'),
    ( 2, 'Tom', 'Permanent', '01/11/2022', 'Ireland'),
    ( 3, 'Mic', 'Permanent', '02/02/2022', 'Ireland'),
    ( 4, 'Conor', 'Permanent', '01/17/2022', 'India'),
    ( 4, 'Conor', 'Contract', '01/13/2022', 'Uk'),
    ( 4, 'Conor', 'Contract', '12/23/2021', 'US'),
    ( 4, 'Conor', 'Contract', '12/12/2021', 'India'),
    ( 4, 'Conor', 'Contract', '12/02/2021', 'Ireland'),
    ( 5, 'Lisa', 'Temporary', '01/07/2021', 'US'),
    ( 5, 'Lisa', 'Contract', '01/02/2021', 'UK'),
    ( 5, 'Lisa', 'Temporary', '12/23/2020', 'India');

CodePudding user response：

with cte1 as 
(
    select Emp_id, Min(Joining_date) as Joining_date
    from TableName
    group by id
),
cte2 as (
    select Emp_id, Emp_Name, Emp_category,Emp_Location
    from TableName
    where Emp_category = 'Permanent'
)
select cte1.Emp_id as Emp_id, cte2.Emp_Name, cte2.Emp_category, cte1.Joining_date, cte2.Emp_Location
from cte1 join cte2 
on cte1.id = cte2.id
order by 1

CodePudding user response：

This can be done in one step using a WINDOW FRAME min operator, and then dropping the non-permanent rows, and ranking the permanent rows, and picking the first (which you didn't mention, but is in the output) via QUALIFY.

SELECT
    emp_id, 
    emp_name,
    emp_category,
    MIN(joining_date::date) OVER (partition by emp_id) as joining_date,
    emp_location
FROM emp_his
QUALIFY emp_category = 'Permanent' 
    AND ROW_NUMBER() OVER (partition by emp_id, emp_category ORDER BY joining_date DESC) = 1
ORDER BY 1;

gives: