CODEWITHSUNDEEP
sql-servert-sql
Andreas
Andreas

Reputation: 865

T-SQL find rows with dates in correct order

I want to somehow mark the rows for each Class-number in the example below that have a row with the same date or rows with dates in order of each other. Have tried to accomplish this way to long but really have nothing of value to share with you...

Having the following sample data:

Date                      Class
2016-10-17 00:00:00.000   1
2016-10-20 00:00:00.000   1
2016-10-18 00:00:00.000   1
2016-10-25 00:00:00.000   1
2016-10-19 00:00:00.000   2
2016-10-19 00:00:00.000   2
2016-10-28 00:00:00.000   2
2016-10-25 00:00:00.000   3

With the logic above, it should produce:

enter image description here

Upvotes: 1

Views: 65

Answers (2)

SteveC
SteveC

Reputation: 6015

This works.

drop table if exists dbo.test_table;
go
create table dbo.test_table(
  [Date]        date not null,
  Class         int not null)

insert dbo.test_table([Date], Class) values
('2016-10-17',1),
('2016-10-20',1),
('2016-10-18',1),
('2016-10-25',1),
('2016-10-19',2),
('2016-10-19',2),
('2016-10-28',2),
('2016-10-25',3);

select tt.*,
       iif(datediff(day, tt.[Date], lead([Date]) over (partition by Class order by [Date])) in (0,1), 1, 0)+
       iif(datediff(day, lag([Date]) over (partition by Class order by [Date]), tt.[Date]) in (0,1), 1, 0) Marked
from dbo.test_table tt;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271121

Hmmm . . . I think you want conditional logic on the results of window functions:

select t.*,
       (case when count(*) over (partition by class, date) > 1
             then 1
             when lag(class) over (partition by class order by date) = dateadd(day, -1, date)
             then 1
             when lead(class) over (partition by class order by date) = dateadd(day, 1, date)
             then 1
             else 0
         end) as mark
from t;

Upvotes: 1

Related Questions