SQL Server group queries the time difference between two adjacent records

Original: SQL Server group Query the time difference between two adjacent records

First, we use two records from the table in the database to elicit problems, such as

These are the table data for a record operation record. OrderID is the self-growing column, followed by operation type, operation time, operator.

The problem now is that you want to filter out all records in the database that have a time difference of more than 2 days from "Receive" to "send out". In the same two documents, the red box is both filtered and the green box is filtered normally.

In order to locate adjacent records, the method is to add an AutoNumber column to the return record of the query statement into the temporary table, and then operate on the staging table.

View Code

--1. First identify all the information in the table that matches the conditionsSelect IDENTITY(int,1,1) asOindex,*  into#temp1 fromOperating RecordswhereOrderIDinch(SelectOrderID fromListwhereOrderNo like 'app%')Order  byorderid,operatedate--2. Signing time delay within 2-7 daysSelectA.orderid,a.operateuser,a.operatename into#temp2 from#temp1 AInner Join#temp1 b onA.orderid=B.orderid andA.operateuser=B.operateuser andA.operatetype='Receive'  andB.operatetype='Send'  andB.oindex=A.oindex+1where DateDiff(dd,a.operatedate,b.operatedate)>2  and DateDiff(dd,a.operatedate,b.operatedate)<=7  andA.operatedate>='2012-06-10'Group  byA.orderid,a.operateuser,a.operatenameOrder  byA.orderid--3. Abnormal and abnormal operator informationSelect *  from#temp2

As you can see, the key is the comparison of the data after the group query.

SQL Server group queries the time difference between two adjacent records