SQL check for 2 equal and sequential values in SQL

Question

I have a table named sapinteraction which has the following structure:

interactionId: String
interactionObject: String
action: String
interactionDate : DateTime

Right now i have query to check the last 2 records of each interactionObject for a specified action.

SELECT max(subTab.interactionDate) AS Maxdate, ssubTab.ts AS MaxMaxDate, subTab.interactionObject,  subTab.action As PrevLastAction , ssubTab.action AS LastAction
FROM sapinteraction  subTab
JOIN(
    SELECT max(subsubTab.interactionDate) ts, subsubTab.interactionObject, subsubTab.action
    FROM sapinteraction  subsubTab
    GROUP BY subsubTab.interactionObject,subsubTab.action
    ) ssubTab ON ssubTab.interactionObject = subTab.interactionObject AND ssubTab.ts > subTab.interactionDate
WHERE  (subTab.s_resultstatus_in <> 1) AND (subTab.interactionDate > '2013-01-01') AND subTab.action = 'DELETE' AND ssubTab.action = 'DELETE'
GROUP BY subTab.interactionObject,subTab.action , ssubTab.action 

Question: I wish to change the query so that retrieve any interactionObject which has the action 'Delete' (example) in twice in a row (according to the datetime). Any idea how to modify my query to achieve this?

As Requested some data (orded by interactionObject, interactionDate)

interactionId   |  interactionObject   |   action             |    interactionDate 
___________________________________________________________________________________________
1093            | Object1              | ADD                  | 2014 - 07 -01
1088            | Object1              | DELETE               | 2014 - 06 -20
1075            | Object1              | ADD                  | 2014 - 05 -13
1066            | Object1              | DELETE               | 2014 - 04 -07
1035            | Object1              | ADD                  | 2014 - 03 -15

1901            | Object2              | ADD                  | 2014 - 07 -01
1807            | Object2              | DELETE               | 2014 - 06 -05
1707            | Object2              | DELETE               | 2014 - 05 -12
1613            | Object2              | ADD                  | 2014 - 04 -13
1500            | Object2              | ADD                  | 2014 - 03 -21

1000            | Object3              | ADD                  | 2014 - 07 -22
1000            | Object3              | DELETE               | 2014 - 06 -19
1000            | Object3              | ADD                  | 2014 - 05 -16
1000            | Object3              | DELETE               | 2014 - 04 -13
1000            | Object3              | DELETE               | 2014 - 03 -10


In the case standing above: i would like to retrieve Object2 & Object3

Answer 1

If we just want to find objects that have had two DELETE interactions in a row, we can do this as:

;With Numbered as (
    SELECT *,ROW_NUMBER() OVER (
       PARTITION BY interactionObject
       ORDER BY interactionDate) as rn
    FROM sapinteraction
)
SELECT distinct n1.interactionObject
FROM
     Numbered n1
        inner join
     Numbered n2
        on
            n1.interactionObject = n2.interactionObject and
            n1.rn = n2.rn - 1
WHERE
   n1.action = 'DELETE' and
   n2.action = 'DELETE'

Which is hopefully straightforward to read.

ROW_NUMBER, WITH Common Table Expression for reference. And I've used DISTINCT on the presumption that even if a particular object has had multiple occurrences of two DELETEs in a row, you only want it to appear in the results once.

This can also easily be changed to find pairs of any actions that happen twice in a row by changing the where clause to n1.action = n2.action.

Answer 2

In SQL Server 2012+ the LAG() function would allow you to do this, but in SQL 2008 I think using an outer apply to get the next row would work. It might perform better if backed by an index on interactiondate likecreate index idx_date on sapinteraction(interactiondate).

select * 
from sapinteraction t1
outer apply (
    select top 1 * 
    from sapinteraction t2 
    where T1.interactionObject = t2.interactionObject
    and t1.interactionDate < t2.interactionDate 
    order by interactionDate
    ) oa
where t1.action = 'DELETE' and oa.action = 'DELETE'

Using a correlated subquery should work too.