SQLTeam.com | Weblogs | Forums

Condition date

Transact-SQL
#1

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

Thank you so much.

date condition
15.1.2020 0
14.2.2020 1
16.4.2020 0
4.9.2020 0
4.11.2020 1
#2

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
#3

i have sql developer. so function datediff do not work

#4

do you have Microsoft sql server, if so what version ?