pair two rows together in sql oracle 10g

Question

I have a below table structure

When the agreement type for the employee is Basic and Appendix (e.g. row 1,2 & 5,6) then these two rows need to be considered together and status would be active. Below should be the expected outcome

How can this be achieved in oracle 10g. Thanks

Answer 1

This can be achieved using a CASE statement and the LEAD analytic function to see if the next ID is Appendix.

Query

--This is to set up the sample data
WITH
    emp_agreements (id, emp_id, agreement_type)
    AS
        (SELECT 1, 1023, 'Basic' FROM DUAL
         UNION ALL
         SELECT 2, 1023, 'Appendix' FROM DUAL
         UNION ALL
         SELECT 3, 1023, 'Basic' FROM DUAL
         UNION ALL
         SELECT 4, 1023, 'Basic' FROM DUAL
         UNION ALL
         SELECT 5, 1023, 'Basic' FROM DUAL
         UNION ALL
         SELECT 6, 1023, 'Appendix' FROM DUAL)
--Real query begins here. You will need to put in your real table name
  SELECT emp_id, status
    FROM (SELECT id,
                 emp_id,
                 agreement_type,
                 CASE LEAD (agreement_type) OVER (PARTITION BY emp_id ORDER BY id)
                     WHEN 'Appendix' THEN 'Active'
                     ELSE 'Pending'
                 END    AS status
            FROM emp_agreements)
   WHERE agreement_type = 'Basic'
ORDER BY id;

Result

   EMP_ID     STATUS
_________ __________
     1023 Active
     1023 Pending
     1023 Pending
     1023 Active