title: Query that recursively compare a record with the previous one
body: <p>I have the following table sample</p>
  
  <pre><code>CREATE TABLE [dbo].[Transactions](
      [TransactionID] [int] IDENTITY(1,1) NOT NULL,
      [TransactionTypeID] [int] NOT NULL,
      [Counter] [int] NOT NULL,
      [GivenCounter] [int] NOT NULL,
      [ExecutionDate] [datetime] NOT NULL,
   CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED ([TransactionID] ASC)
  </code></pre>
  
  <p>The <code>TransactionTypeID</code> field identify the type of transaction:</p>
  
  <ul>
  <li>1 means Activation</li>
  <li>2 means Consumption</li>
  <li>3 means Cancellation</li>
  </ul>
  
  <p>In particular the <code>type 3</code> refers to the cancellation of the previous transaction. So if the transaction which has been executed prior to cancellation (in terms of ExecutionDate) is a <code>type 2</code> means the cancellation of a consumption while if the previous transaction is a <code>type 1</code> means that the cancellation is a de-activation.</p>
  
  <p>How can I query only for transactions which the meanings is deactivation (<code>type 3</code> with previous <code>type 1</code>)?
  Can anyone help please?</p>
  
  <p>EDIT: Added some sample data to better explain</p>
  
  <pre><code>TransactionID   TransactionTypeID   Counter  GivenCounter    ExecutionDate  
  --------------  -----------------   -------  ------------    -----------------------
  138327              1               1          0             2014-07-20 07:14:08.000
  134175              2               0          1             2014-07-21 12:39:12.000
  137620              3               1          1             2014-07-22 20:19:06.000
  134199              1               1          0             2014-07-23 10:09:52.000
  132772              3               0          1             2014-07-24 08:18:23.000
  </code></pre>
  
  <p>In this example my query should extract only one record (<code>TransactionID</code> 132772) because it is a cancellation of the previous transaction that had <code>TransactionTypeID</code> equals to 1</p>