how to compare two consecutive rows in the same table in sql sever

few things:
1. is the identity an integer? if so, can you rely on a greater value to being the 2nd row
2. if you have four rows, would you look at 1,2 and 3,4, or would you look at 1,2, and 2,3 and 3,4?
3. can you provide maybe 10 records so we can see the dataset

BC

@bchoor:
1. No the identity is not an integer
2. 1,2  2,3 3,4
3. No, because of security reasons.

You can use the Row_Number() function in SQL 2005 and 2008

for example

select Row_Number(),Name,Address from Contacts

between two consecutive rows.
Consecutive = ordered by date?

You could do:

--number the fields in date order
;WITH cte AS
{
   SELECT *, ROW_NUMBER() OVER(PARTITION BY DateField ORDER BY [Order] DESC) As [rowNum]
   FROM myTable
}
--JOIN on consecutive rows with matchign criteria
select cte1.uniqueID AS [firstID],
       cte1.category AS [firstCategory],
       cte2.uniqueID AS [secondID]
       cte2.category AS [secondCategory]
from cte1
JOIN cte2
  ON cte2.rowNum = cte1.rowNum + 1
  AND DATEDIFF(dd,cte1.dateField,cte2.dateField) < 1
  AND cte1.[transaction code]  = 'A'
  AND cte2.[transaction code]  = 'r'

Select allOpen in new window

select a.*, b.*
from tbl a
outer apply (
  select top 1 * from tbl c
  where c.category = a.category and c.date > a.date
  order by c.date asc) b
where a.transactioncode = 'A' and b.transactioncode = 'R'
  and dateadd(d, 1, A.date) > b.date

Hope this is what you need:

;with cte as (
SELECT unique_id, category, [date], [transaction], ROW_NUMBER() over ( partition by category order by date) rnum
From ur_table) 
select t1.unique_id, t1.category, t1.[date], t1.[transaction]
from cte t1 left join cte t2 on t1.category = t2.category and t1.rnum = t2.rnum - 1
where datediff(dd, t1.[date], t2.[date]) = 1 
and t1.[transaction] = 'A'
and t2.[transaction] = 'R'

Select allOpen in new window

@bodestone

  AND DATEDIFF(dd,cte1.dateField,cte2.dateField) < 1

datediff(dd, '20100101 23:59', '20100102 00:01') => 1

Small mistake:

;with cte as (
SELECT unique_id, category, [date], [transaction], ROW_NUMBER() over ( partition by category order by date) rnum
From ur_table) 
select t1.unique_id, t1.category, t1.[date], t1.[transaction]
from cte t1 left join cte t2 on t1.category = t2.category and t1.rnum = t2.rnum - 1
where datediff(dd, t1.[date], t2.[date]) <= 1 
and t1.[transaction] = 'A'
and t2.[transaction] = 'R'

Select allOpen in new window

Well if they are consecutive rows, then doing any other "order by" will take them out of sequence... My understanding and you have reiterated is you want to compare row 1 with row 2 then row 2 with row 3 etc...

So, if using the row_number() function then it should be simply ordered by unique_id to keep the sequence. Assuming of course the "unique identity" is an identity column and not just a guid (unless added  using new sequential id).

What isnt clear is exactly what you want to extract.... You say category, but assume you want more than that....

There are two ways, but based on table size, we need to consider the impacts of that table size.

So, looking at CTE and Outer apply, and then a fairly straight subquery... The last one on a small data set seems to be most efficient. Of course it is based on the above assumptions, and also assumes there are indexes to suit (and we should probably talk about what indexes are available).

-- example 1 with Outer Apply
select a.*, b.*
from #tbl a
outer apply (
  select top 1 * from #tbl c
  where c.category = a.category 
  and c.date > a.date
  and c.unique_id > a.unique_id
  order by c.unique_id) b
where a.transactioncode = 'A' and b.transactioncode = 'R'
  and datediff(d, A.date, B.date) = 1

-- example 2 with CTE query 1

;with cte as (
SELECT unique_id, category, date, transactioncode, ROW_NUMBER() over (order by unique_id) rn
From #tbl) 
select t1.unique_id, t1.category, t2.unique_id
from cte t1 
left join cte t2 on t1.category = t2.category and t1.rn = t2.rn - 1
where datediff(dd, t1.[date], t2.[date]) = 1 
and t1.transactioncode = 'A'
and t2.transactioncode = 'R'

-- example 3 with simple subquery

select * from
(
select a.unique_id, a.category, (select min(unique_id) from #tbl c
             where c.category = a.category 
             and datediff(d, a.date, c.date) = 1
             and c.transactioncode = 'R'
             and c.unique_id > a.unique_id)  as next_unique_id
  
from #tbl a
where a.transactioncode = 'A'
) b 
where next_unique_id is not NULL

-- example 4 the above but as a CTE query

;with cte2 as (
select a.unique_id, a.category, (select min(unique_id) from #tbl c
             where c.category = a.category 
             and c.transactioncode = 'R'
             and datediff(d, a.date, c.date) = 1
             and c.unique_id > a.unique_id)  as next_unique_id
  
from #tbl a
where a.transactioncode = 'A'
) 
select * from cte2 where next_unique_id is not NULL

Select allOpen in new window

ooops that example 1 above should have been :


select a.*, b.*
from #tbl a
outer apply (
select top 1 transactioncode,date from #tbl c
where c.category = a.category
and c.unique_id > a.unique_id
order by c.unique_id) b
where a.transactioncode = 'A'
and b.transactioncode = 'R'
and datediff(d, A.date, b.date) = 1

And maybe that CTE query is best as :


;with cte2 as (
select a.unique_id, a.category, a.date, (select min(unique_id) from #tbl c where c.unique_id > a.unique_id) as next_unique_id
from #tbl a
where a.transactioncode = 'A'
)
select *
from cte2 a
inner join #tbl b on a.next_unique_id = b.unique_id and a.category = b.category
where datediff(d, a.date, b.date) = 1
and b.transactioncode = 'R'

Again, a lot of it depends in various indexes....

thanks