score:2

Accepted answer

The most straightforward way to do this is to use a subquery:

select A.number, a.start_date, a.end_date,
    CASE WHEN start_date < dateadd(d,30,(select TOP(1) b.end_date
     from mytable B
     where B.number < A.number
     order by B.number desc)) then 1 else 0 end Criteria
from mytable A

Note: If the start date is the 29th day following the previous row's end date, Criteria becomes 1. By the 30th day onwards, it is 0. Tweak the 30 in the query as required.

Sample:

create table mytable (
    Number int primary key,
    start_date datetime,
    end_date datetime);

insert mytable
select 1, '2011-06-07', '2011-07-10' union all
select 2, '2011-10-11', '2011-10-27' union all
select 3, '2011-10-26', '2011-10-29' union all
select 4, '2011-10-29', '2011-11-15'

Result:

number  start_date  end_date    Criteria
1   2011-06-07 00:00:00.000 2011-07-10 00:00:00.000 0
2   2011-10-11 00:00:00.000 2011-10-27 00:00:00.000 0
3   2011-10-26 00:00:00.000 2011-10-29 00:00:00.000 1
4   2011-10-29 00:00:00.000 2011-11-15 00:00:00.000 0

Read More

score:0

A more readable way is create a function and send the correct dates:

Function:

create function [dbo].[CompareDates] (
    @START_DATE datetime,
    @PREVIOUS_END_DATE datetime
)
RETURNS int
AS
BEGIN

    if @START_DATE < @PREVIOUS_END_DATE
        return 1
    return 0

END

Query (using subquery):

declare @dates table
(
    number int,
    start datetime,
    end_date datetime
)

insert into @dates values
(1, '2011-06-07 00:00:00.000', '2011-07-10 00:00:00.000'),
(2, '2011-10-11 00:00:00.000', '2011-10-27 00:00:00.000'),
(3, '2011-10-26 00:00:00.000', '2011-10-29 00:00:00.000'),
(4, '2011-10-29 00:00:00.000', '2011-11-15 00:00:00.000')

select *, dbo.CompareDates(dates.end_date, dates.previous_end_date)  from
(
    select number, start, end_date,
        (select TOP 1 end_date
        from @dates d2
        where d2.number < d1.number
        order by d2.number desc) as previous_end_date
    from @dates d1
) dates

score:1

Try using case like this:

create view vDates as
select Number,start_date,end_date, 
       case 
         when start_date<end_date 
           then 0
         else 1
       end as Criteria
from tab

SQL Fiddle Demo


More questions

More questions with similar tag