This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Find the date gaps and count

New Roots
New Roots Member Posts: 188 Blue Ribbon
edited Aug 27, 2019 9:22AM in SQL & PL/SQL

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*/
Tagged:
New RootsBrunoVroman

Best Answer

  • mNem
    mNem Perpetual Learner -Member Posts: 1,380 Gold Trophy
    edited Aug 27, 2019 8:36AM Answer ✓

    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

    ;

    New Roots

Answers

  • mNem
    mNem Perpetual Learner -Member Posts: 1,380 Gold Trophy
    edited Aug 27, 2019 8:36AM Answer ✓

    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

    ;

    New Roots
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    edited Aug 27, 2019 8:42AM

    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.

    New RootsNew Roots
  • BrunoVroman
    BrunoVroman BelgiumMember Posts: 1,848 Silver Crown
    edited Aug 27, 2019 8:53AM

    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 RootsNew Roots
  • New Roots
    New Roots Member Posts: 188 Blue Ribbon
    edited 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
    New Roots Member Posts: 188 Blue Ribbon
    edited 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.

    BrunoVroman
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    edited Aug 27, 2019 9:22AM

    This might be easier to understand:

    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 (s x*)

                      define s as projectcode is null,

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

                     )

    /

    SY.

    New RootsNew Roots