Bdtjtk

Emp_no | from_date  | To_date   | Initial_date

-------+------------+-----------+-------------

234    | 01-Dec-18  | 10-Dec-18 | 01-Dec-18     

234    | 11-Dec-18  | 05-Jan-19 | 01-Dec-18     

234    | 06-Jan-19  | 20-Jan-19 | 01-Dec-18     

234    | 21-Jan-19  | 25-Jan-19 | 01-Dec-18 

I just want to get the initial date of further extended leaves as all the entries will be treated as one initial date. How can I get the above mentioned result with column Initial_date?

This is to add an extra functionality to calculate the total authorized or earned leave of Service in the leave management software, as in each month 4 leaves has been earned by employees with the following conditions:

1. 16 days attendance is necessary in a month to earn the leaves.


2. If leave connects two months so the earning of one of the month will be
   lost and the earning of other month depends on the 1st condition of 16 days attendance.

So I just want to create a logic to sort out these kinds of leave which has been taken and further extended to more days which results multiple entries of continued leaves.

Not sure if I understood your requirements, but let's try:

select emp_no, from_date, to_date, level lvl,

       connect_by_root(from_date) initial_date

  from tbl t1

  start with 

    not exists (

      select 1 

        from tbl t2 

        where t1.emp_no = t2.emp_no and t1.from_date = t2.to_date + 1)

  connect by emp_no = prior emp_no and from_date = prior to_date + 1

^{dbfiddle demo}

Using these sample data:

with tbl(emp_no, from_date, to_date) as (

    select 234, date '2018-12-01', date '2018-12-10' from dual union all

    select 234, date '2018-12-11', date '2019-01-05' from dual union all

    select 234, date '2019-01-06', date '2019-01-20' from dual union all

    select 234, date '2019-01-21', date '2019-01-25' from dual union all

    -- not continuous dates 

    select 234, date '2019-03-10', date '2019-03-17' from dual union all 

    select 234, date '2019-03-18', date '2019-03-25' from dual union all 

    -- other emp_no

    select 407, date '2018-12-15', date '2019-01-07' from dual )

... we get this result:

EMP_NO FROM_DATE   TO_DATE            LVL INITIAL_DATE

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

   234 2018-12-01  2018-12-10           1 2018-12-01

   234 2018-12-11  2019-01-05           2 2018-12-01

   234 2019-01-06  2019-01-20           3 2018-12-01

   234 2019-01-21  2019-01-25           4 2018-12-01

   234 2019-03-10  2019-03-17           1 2019-03-10

   234 2019-03-18  2019-03-25           2 2019-03-10

   407 2018-12-15  2019-01-07           1 2018-12-15

If this is not what you needed please edit your question and show required output matching more complicated sample data (for instance my sample above).

Explanation: Hieriarchical query (connect by) finds for every emp_no dates which are not continuous and starting from these points attaches next continuous rows. Operator connect_by_root is used to present initial_date.

Try something along the lines of

select emp_id, from_date, to_date, (select min(from_date) 

                                    from TABLE_NAME T1 

                                    where t1.emp_id = t.emp_id) as inital_date

from TABLE_NAME T

where emp_id = 234;