Hello I am new in SQL developer .Can you help me create condition in sql. I have date in first column but i need create numbers in second columns. First number in second columns have to be zero but next data between first number and first number +90 have to be one.
the first number on the first date must be 0. if the date between the first date and the first date is +90 then it is 1 if it is higher than 0, the third date is 0 because it is more than the first date +90, the fourth date is 0 because it is more than the third date +90 and the fifth date is 1 because between the fourth date and the fourth date it is +90
Not sure why the 3rd date would be 0 since shouldn't we be comparing it to the 2nd date and not the 1st date. That is what is being done with the 4th and 5th dates. Try using Lag
Create table #t (CompareDate date)
insert into #t values
('1/15/2020'),
('2/14/2020'),
('4/16/2020'),
('9/4/2020'),
('11/4/2020')
select *, case when DateDiff(day, Lag(CompareDate) Over (order by CompareDate), CompareDate) <= 90 then 1 else 0 end Condition
from #t
order by compareDate