CODEWITHSUNDEEP
sqldb2
Cappa
Cappa

Reputation: 117

SELECT which ignores all NULL findings and their relatives

I have the following table structure:

NAME        DEPT        IN_DEPT      OUT_DEPT
J. Smith    Fin/Team1   2014-05-10   NULL
J. Smith    Fin/Team2   2012-07-08   2014-05-09    
J. Smith    Fin/Team4   2011-10-11   2012-07-07
I. Ivanov   Acc/Team2   2015-03-05   NULL
I. Ivanov   Fin/Team2   2011-02-08   2015-03-04

I intend to do a timeline of the Finance Dept mobility (external - not inside teams). So, i already did a SELECT MIN(IN_DEPT), which is quite easy, and i need now to find the MAX(OUT_DEPT) to UNION findings.

But in that case, J. Smith is still in the Finance dept., so the MAX date is not fine.

I'm having trouble to make this query, when if there are a NULL value in the OUT_DEPT and the DEPT value begins with 'Fin', it throw out every other findings of the NAME.

The goal table result might be like this:

NAME        DATE         ACTION
I. Ivanov   2015-03-04   OUT
J. Smith    2011-10-11   IN
I. Ivanov   2011-02-08   IN

Any help appreciated. Thanks.

Upvotes: 1

Views: 96

Answers (2)

conspicillatus
conspicillatus

Reputation: 335

This should work. First select the minimum of in dates. Next those dates are unioned with the maximum of out dates, where only people are regarded who have left the department. This means there must not exist a name in a department without an OUT_DEPT.

SELECT NAME, MIN(IN_DEPT) DATE, 'IN' ACTION
FROM DEPARTMENT
WHERE DEPT LIKE 'Fin/%'
GROUP BY NAME

UNION ALL

SELECT D0.NAME, MAX(D0.OUT_DEPT) DATE, 'OUT' ACTION
FROM DEPARTMENT D0
WHERE DEPT LIKE 'Fin/%'
  AND NOT EXISTS (
      SELECT 1
      FROM DEPARTMENT D1
      WHERE D1.NAME = D0.NAME
        AND D1.DEPT LIKE 'Fin/%'
        AND D1.OUT_DEPT IS NULL )
GROUP BY NAME

However, condition with NOT EXISTS looks better if you split department and team or select only records from finance department in a CTE.

WITH DEPARTMENT_FIN (NAME, IN_DEPT, OUT_DEPT) AS (
  SELECT NAME
        ,IN_DEPT
        ,OUT_DEPT
  FROM DEPARTMENT
  WHERE DEPT LIKE 'Fin%'
)

SELECT NAME, MIN(IN_DEPT) DATE, 'IN' ACTION
FROM DEPARTMENT_FIN
GROUP BY NAME

UNION ALL

SELECT D0.NAME, MAX(D0.OUT_DEPT) DATE, 'OUT' ACTION
FROM DEPARTMENT_FIN D0
WHERE NOT EXISTS (
      SELECT 1
      FROM DEPARTMENT_FIN D1
      WHERE D1.NAME = D0.NAME
        AND D1.OUT_DEPT IS NULL )
GROUP BY NAME

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You need to identify when people enter finance and leave. This is tricky. Here is one method using lag() and lead():

with cte as (
      select t.*
      from (select t.*,
                   lag(out_dept) over (partition by name order by in_dept) as prev_out,
                   lead(in_dept) over (partition by name order by in_dept) as next_in
            from t
            where dept like 'Fin%'
     )
select name, in_dept, 'IN'
from t
where prev_out is null or prev_out <> in_dept - 1 day
union all
select name, out_dept, 'OUT'
from t
where next_in is null or next_in <> out_dept + 1 day;

Upvotes: 1

Related Questions