Find the date gaps and count

New Roots · Aug 27, 2019 8:13AM

Hi ,

Below is the table structure and sample data . I just want to find the the number of project unassigned periods for the employee. Could you please help on writing the query to fetch the expected result ?

with t(id,emp_code,projectcode,dates)as (select 1,100,'p1',to_date('10-jan-2019') from dual union all select 2,100,'p1',to_date('11-jan-2019') from dual union all select 3,100,'p1',to_date('12-jan-2019') from dual union all select 4,100,null,to_date('13-jan-2019') from dual union all select 5,100,null,to_date('14-jan-2019') from dual union all select 6,100,'p2',to_date('15-jan-2019') from dual union all select 7,100,'p2',to_date('16-jan-2019') from dual union all select 8,100,null,to_date('17-jan-2019') from dual union all select 9,100,null,to_date('18-jan-2019') from dual union all select 10,100,'p2',to_date('19-jan-2019') from dual union all select 10,100,null,to_date('20-jan-2019') from dual)select * from t;/*---------------------------Below is  my Expected resultEmp_code     start_dt     end_dt      count----------------------------------------------100          13-JAN-19    14-JAN-19    2100          16-JAN-19    17-JAN-19    2100          20-JAN-19    20-JAN-19    1*/

mNem · Aug 27, 2019 8:36AM

See if this is what you are looking for ...

select emp_code, min(dates) mn, max(dates) mx, max(dates) - min(dates) + 1 cnt from

(

select t.*, dates - row_number() over (partition by emp_code order by dates) rn from t

where projectcode is null and dates is not null

)

group by emp_code, rn

order by 1, 2

;

mNem · Aug 27, 2019 8:36AM

See if this is what you are looking for ...

select emp_code, min(dates) mn, max(dates) mx, max(dates) - min(dates) + 1 cnt from

(

select t.*, dates - row_number() over (partition by emp_code order by dates) rn from t

where projectcode is null and dates is not null

)

group by emp_code, rn

order by 1, 2

;

Solomon Yakobson · Aug 27, 2019 8:40AM

with t(id,emp_code,projectcode,dates)

as (select 1,100,'p1',to_date('10-jan-2019') from dual union all

select 2,100,'p1',to_date('11-jan-2019') from dual union all

select 3,100,'p1',to_date('12-jan-2019') from dual union all

select 4,100,null,to_date('13-jan-2019') from dual union all

select 5,100,null,to_date('14-jan-2019') from dual union all

select 6,100,'p2',to_date('15-jan-2019') from dual union all

select 7,100,'p2',to_date('16-jan-2019') from dual union all

select 8,100,null,to_date('17-jan-2019') from dual union all

select 9,100,null,to_date('18-jan-2019') from dual union all

select 10,100,'p2',to_date('19-jan-2019') from dual union all

select 10,100,null,to_date('20-jan-2019') from dual)

select emp_code,

start_dt,

end_dt,

cnt

from t

match_recognize(

partition by emp_code

order by id

measures first(dates) as start_dt,

last(dates) as end_dt,

count(*) as cnt

pattern (x+)

define x as projectcode is null and nvl(prev(dates),dates - 1) = dates - 1

)

/

EMP_CODE START_DT END_DT CNT

---------- --------- --------- ----------

100 13-JAN-19 14-JAN-19 2

100 17-JAN-19 18-JAN-19 2

100 20-JAN-19 20-JAN-19 1

SQL>

SY.

BrunoVroman · Aug 27, 2019 8:50AM

Hello New Roots,

simply a few remarks:

-1-
note that TO_DATE( '26-jan-2019' ) assumes that the format of the date is DD-mon-YYYY... the day the format uses another session you are in trouble.
Avoid using implicit formats,
prefer things like TO_DATE( '26-JAN-2019', 'DD-MON-YYYY' ),
or, better, (as the language might cause issues with MON) use TO_DATE( '26-01-2019', 'DD-MM-YYYY' )
or, even better/simpler: ISO format: DATE '2019-01-26' (keyword DATE followed by date in format YYYY-MM-DD between single quotes)

-2-
I am surprised by your datamodel... It seems that "t" contains all the dates without gaps, making counts easy but datamodel not likely to resist to actual world data

Examples:
what if we want to add something like "on 13-JAN, project p3 for emp_code 100": we have to UPDATE the existing row with project= null
what if we want to add something like "on 23-JAN, project p1 fo remp_code 100": we have to add the rows for 21, 22 and 23-JAN?
what if we want to add something like "on 04-FEV, project p4 for emp_code 123": first time we have thsi emp_code; do we have to add other dates with project = null? which ones?
what if we want to remove a row ((I see a problem present in your data so I can't phrase the question adequately))
... Too many questions for the model to be OK I think.

More usual is to store only the "relevant" data, not the lines with "empty project".
Then with the usual way of working, the "trick" is to generate a list of all relevant dates (maybe between MIN( dates ) and MAX( dates ) observed for a given emp_code, maybe for MIN and MAX obeserved through all the emp_codes, maybe arbitrarily (like 01-JAN-2019 to 31-JAN-2019), ...
And to query this generated list, using a LEFT OUTER JOIN to the actual data, this is a dynamic way to "fill in the list" and to go back to your original list with "project = null"
And then we might start from this and query only the NULLs, or count them, or ...

For this last part you can see other replies (people replying to your question stricto sensu)

Best regards,

Bruno Vroman.

P.S. to test the queries you should generate more cases in your input data; add more emp_codes, add dates where more than 1 emp_code have a project, add dates where 1 emp_code has more than 1 project, add 1 emp_code having only 1 date (with a project_id), add 1 emp_code having only 1 date (with NULL project_id).

New Roots · Aug 27, 2019 8:50AM

Hi Solomon,

Thank you for the different approach . I am going to spend some time to understand the match_recognize .

New Roots · Aug 27, 2019 8:58AM

Hi Bruno,

Thank you very much for such wonderful review. This model is exists in our system for while, and I could not correct this stage. But I really keep in mind your inputs while design new data model. Really appreciated . Thanks.

Solomon Yakobson · Aug 27, 2019 9:21AM

This might be easier to understand: