compare next record in the same table sql

Question

I have a table having 2 columns trans_date and amount. I want to a query that give me the amount if the transdate diff of a record and the next record is 1 day or same day.

explanation:

AMOUNT    TRANS_DATE

2645    2011-05-11 20:57:27.000
2640    2011-05-12 00:00:00.000
2645    2011-05-15 18:01:11.000
2645    2011-06-15 18:27:45.000
2645    2011-06-16 17:06:33.000
2645    2011-06-18 15:19:19.000
2645    2011-06-23 15:42:18.000

the query should show me only

 AMOUNT    TRANS_DATE

    2645    2011-05-11 20:57:27.000
    2640    2011-05-12 00:00:00.000
    2645    2011-05-15 18:01:11.000
    2645    2011-06-15 18:27:45.000
    2645    2011-06-16 17:06:33.000

all i have tried is

select DATEDIFF(DAY,a.TRANS_DATE,b.TRANS_DATE) from FIN_AP_PAYMENTS a inner join ( select * from (select a.*,rank() over (order by id) as ra from FIN_AP_PAYMENTS a, FIN_AP_PAYMENTS b )tbl )



select a.TRANS_DATE,b.TRANS_DATE,rank() over (order by a.id) as ra1,rank() over (order by b.id) as ra2 from FIN_AP_PAYMENTS a,FIN_AP_PAYMENTS b



select DATEDIFF(day,tbl.TRANS_DATE,tbl2.TRANS_DATE) from (select a.*,rank() over (order by id) as ra from FIN_AP_PAYMENTS a) tbl inner join (select a.*,rank() over (order by a.id) as ra1 from FIN_AP_PAYMENTS a ) tbl2 on tbl.id=tbl2.id

Answer 1

Use lead() and lag() to get the next and previous values. Then check the timing between them for filtering:

select t.amount, t.trans_date
from (select t.*, lead(trans_date) over (order by trans_date) as next_td,
             lag(trans_date) over (order by trans_date) as prev_td
      from FIN_AP_PAYMENTS t
     ) t
where datediff(second, prev_td, trans_date) < 24*60*60 or
      datediff(second, trans_date, next_trans_date) < 24*60*60;

EDIT:

In SQL Server 2008, you can do this using outer apply:

select t.amount, t.trans_date
from (select t.*, tlead.trans_date as next_td,
             tlag.trans_date as prev_td
      from FIN_AP_PAYMENTS t outer apply
           (select top 1 t2.*
            from FIN_AP_PAYMENTS t2
            where t2.trans_date < t.trans_date
            order by trans_date desc
           ) tlag outer apply
           (select top 1 t2.*
            from FIN_AP_PAYMENTS t2
            where t2.trans_date > t.trans_date
            order by trans_date asc
           ) tlead         
     ) t
where datediff(second, prev_td, trans_date) < 24*60*60 or
      datediff(second, trans_date, next_trans_date) < 24*60*60;