SQL How to assign correct dates to multiple nulls in sequential date columns

I have a range of sequential dates for an employee and there are nulls in between. The logic to handle nulls is as mentioned below:

if todate is null and next record fromdate has a value then todate is (next record fromdate-1)
if todate is null and there is no next record fromdate then default todate to 01/08/2020

Current Data

Required Data

Answer

This should get you started but as you don’t state which dbms you are using the syntax for deducting one day is subject to lots of variation.

CREATE TABLE mytable(
   FROMDATE DATE  NOT NULL
  ,TODATE   DATE 
);
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('15/02/2016','30/04/2016');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/05/2016',NULL);
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('2/06/2016','16/10/2016');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('17/10/2016','31/12/2016');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2017','14/02/2017');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('15/02/2017','31/12/2017');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2018','14/02/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('15/02/2018','20/02/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('21/02/2018','31/03/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/04/2018','31/12/2018');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2019','31/03/2019');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/04/2019','31/12/2019');
INSERT INTO mytable(FROMDATE,TODATE) VALUES ('1/01/2020',NULL);

select
    fromdate
  , todate                                                        as todate_raw
  , coalesce(todate,lag(fromdate,1) over(order by fromdate DESC)) as todate_new
from mytable
order by fromdate

fromdate   | todate_raw | todate_new
:--------- | :--------- | :---------
2016-02-15 | 2016-04-30 | 2016-04-30
2016-05-01 | null       | 2016-06-02
2016-06-02 | 2016-10-16 | 2016-10-16
2016-10-17 | 2016-12-31 | 2016-12-31
2017-01-01 | 2017-02-14 | 2017-02-14
2017-02-15 | 2017-12-31 | 2017-12-31
2018-01-01 | 2018-02-14 | 2018-02-14
2018-02-15 | 2018-02-20 | 2018-02-20
2018-02-21 | 2018-03-31 | 2018-03-31
2018-04-01 | 2018-12-31 | 2018-12-31
2019-01-01 | 2019-03-31 | 2019-03-31
2019-04-01 | 2019-12-31 | 2019-12-31
2020-01-01 | null       | null      

db<>fiddle here