add column as End_date with +100 days interval in SQL

user_18576551 0
Viewed 27

I am trying to solve this. table has cycle and date column, i wanna add one more column as end date with interval of 100 days from current date and wanna display cycle, start_date, end_date as an output.

i have tried this

select cycle, min(start) as startt, max(start) as endd  
from cy 
group by cycle ;

however no luck till now.

its MYSQL DB latest version.

CREATE TABLE table1 (
  `cycle` INTEGER,
  `date` DATE,
);

INSERT INTO table1
  (`cycle`, `date`)
VALUES
  ('1', '15-Jan-21'),
  ('2', '01-Aug-21'),
  ('3', '08-sep-21'),
  ('4', '15-Dec-21');

Output : enter image description here

mysql
1 Answers

If I understand correctly, you might want to use LEAD window function with DATE_SUB if your MySQL version supports that.

SELECT *
FROM (
 SELECT *,DATE_SUB(LEAD(`date`) OVER(ORDER BY cycle), INTERVAL 1 DAY)  endDate
 FROM table1
) t1
WHERE endDate IS NOT NULL

sqlfiddle

user_51760710
Related