Tutorial :SQL question: Getting records based on datediff from record to record


Ok, got a tricky one here... If my data looks like this:


ID  Date_Created   1   1/1/2009  2   1/3/2009  3   1/5/2009  4   1/10/2009  5   1/15/2009  6   1/16/2009  

How do I get the records that are 2 days apart from each other? My end result set should be rows 1-3, and 5-6. Thanks!


select distinct t1.*  from Table1 t1  inner join Table1 t2       on abs(cast(t1.Date_Created - t2.Date_Created as float)) between 1 and 2  


SELECT l.*  FROM Table1 l  INNER JOIN Table1 r ON DATEDIFF(d, l.Date_Created, r.Date_Created) = 2        AND r.Date_Created = (SELECT TOP 1 * FROM Table1 WHERE Date_Created > l.Date_Created ORDER BY Date_Create)  


-- what does this give you?

select DISTINCT t1.id, t1.date_created, t2.id, t2.date_created from table1 t1, table1 t2 where datediff(dd,t1.date_created,t2.date_created) = 2 AND t1.id != t2.id ORDER BY t1.id;


Would this work?

select t1.id, t2.id     from table1 t1     join table1 t2       on t2.date_created - t1.date_created <= 2  


I might suggest using programming code to do it. You want to collect groups of rows (separate groups). I don't think you can solve this using a single query (which would give you just one set of rows back).


If you want to get the rows which are WITHIN 'N' days apart, you can try this:

select t1.date_created, t2.date_created   from table1 t1, table1 t2   where t1.id <> t2.id and         t2.date_created-t1.date_created between 0 and N;  

for exmaple, as you said, if you want to get the rows which are WITHIN 2 days a part, you can use the below:

select t1.date_created,t2.date_created   from table1 t1, table1.t2   where t1.id <> t2.id and         t2.date_created-t1.date_created between 0 and 2;  

I hope this helps....

Regards, Srikrishna.


A cursor will be fastest, but here is a SELECT query that will do it. Note that for "up to N" days apart instead of 2 you'll have to replace the table Two with a table of integers from 0 to N-1 (and the efficiency will get worse).

I'll admit it's not entirely clear what you want, but I'm guess you want the ranges of rows that contain at least two rows in all and within which the successive rows are at most 2 days apart. If dates increase along with IDs, this should work.

with Two as (    select 0 as offset union all select 1   ), r2(ID, Date_Created_o, dr) as (    select      ID, Date_Created+offset,      Date_Created + offset - dense_rank() over (        order by Date_Created+offset      ) from r cross join Two  )    select      min(ID) as start, max(ID) as finish    from r2    group by dr    having min(ID) < max(ID)    order by dr;  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »