Breaking News: Grepper is joining You.com. Read the official announcement!

sql query for employee table where employee have gap in employment

Frantic Falcon answered on August 23, 2022 Popularity 1/10 Helpfulness 1/10

answer sql query for employee table where employee have gap in employment

sql query for employee table where employee have gap in employment

Comment

select 
    src.*, 
    src.start_date - src.prev_end_date gap_days 
  from (
    select 
        out_tab.emp_no, 
        (select max(in_tab.end_date) from ABC in_tab where in_tab.emp_no = out_tab.emp_no and in_tab.end_date < out_tab.start_date) prev_end_date, 
        out_tab.start_date 
      from 
        ABC out_tab 
  ) src
  where 
    start_date - prev_end_date > 1;


    EMP_NO PREV_END_DATE START_DATE   GAP_DAYS
---------- ------------- ---------- ----------
         1 29-AUG-10     20-OCT-10          52 
         3 21-MAR-10     29-MAR-10           8

Popularity 1/10 Helpfulness 1/10 Language sql

Source: stackoverflow.com

Tags: sql where

Link to this answer
Share Copy Link