how to get emp id for any change values happend after first record-CodePudding

I have emp table it contains many colmuns and an employee can have many rows beside on the
changed happend to his/her records . it hasnt primary key because emp id can
repeated beside on employee values .

there's a column "health" ,it decribes the health and with values(heart,skin,null) etc..
and modification_date for each change of values in a health column let's say employee number 1 has a heart problem as a first record registed in a health column then the employee got well then added a second row and column health=null ,
after sometimes the employee got sick to a nother disease 'skin'

how to get employee number if his/her column(health)
has been change to any values of health if values become null or other values ?

any help please ?

select empid, health_status from
(
    select e.emp_id empid, e.health health_status,
           count(e.health) over (partition by e.emp_id order by e.modification_date asc) sick_count
    from emp e
)
where sick_count > 1

CodePudding user response：

Seems you need counting NULLs and NOT-NULLs. NVL2() function would suit well in order to compute this such as

SELECT e.emp_id, e.health,
       SUM(NVL2(health,1,0)) OVER (PARTITION BY e.emp_id) AS "Sick",
       SUM(NVL2(health,0,1)) OVER (PARTITION BY e.emp_id) AS "Got well"       
  FROM emp e

if the health is NOT-NULL then second argument will return, otherwise the third argument will return. Btw, using an ORDER BY clause will be redundant.

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to find the employees who were sick, got well and then got sick again:

SELECT emp_id
FROM   emp
MATCH_RECOGNIZE (
  PARTITION BY emp_id
  ORDER     BY modification_date
  PATTERN (sick well  sick)
  DEFINE
    sick AS health IS NOT NULL
    well AS health IS NULL
);