Many of us have faced a problem where we need to compare historical records for a particular set of records to ensure that records have been saved in the correct order.

This could be for comparisons of dates or numbers or anything.  The need is to ensure that the historical records have been saved in the correct order. Using Cross joins, we can identify records that have been stored out of order for a subset of records.

Let’s say I have table in which there are three fields:

EmployeeId
Version
CreateDate

Here, EmployeeId is an integer field and is our primary ID. Version is also an integer field,considering this as a secondary ID. So, in totality EmployeeId and Version will form the Composite Key. There can be many versions of the same EmployeeId and each Version would have a corresponding CreateDate.

Our problem is to compare the CreateDate of all the versions of a particular employee on an iterative basis using a single query to ensure the CreateDate are in order based on the Version for a particular employee.

The below scripts will help you understand the problem and its solution.  I am using a new table, adding some data and then using the query to determine which records are out of order.

   1: CREATE TABLE Employees(
   2:      EmployeeID INT        NOT NULL,
   3:      Version    INT        NOT NULL,
   4:      CreateDate DATETIME   NULL,
   5: CONSTRAINT pk_Employees PRIMARY KEY CLUSTERED 
   6: (
   7:      [EmployeeID] ASC,
   8:      [Version] ASC
   9: ))
  10: GO
  11: INSERT INTO Employees VALUES(1,0,'03/10/2000')
  12: INSERT INTO Employees VALUES(1,1,'03/16/2000')
  13: INSERT INTO Employees VALUES(1,2,'03/19/2000')
  14: INSERT INTO Employees VALUES(1,3,'03/18/2000')
  15: INSERT INTO Employees VALUES(1,4,'03/17/2000')
  16: INSERT INTO Employees VALUES(2,0,'02/10/2000')
  17: INSERT INTO Employees VALUES(2,1,'02/11/2000')
  18: INSERT INTO Employees VALUES(2,2,'02/18/2000')
  19: INSERT INTO Employees VALUES(3,0,'03/25/2000')
  20: INSERT INTO Employees VALUES(3,1,'03/23/2000')
  21: INSERT INTO Employees VALUES(3,2,'03/26/2000')
  22: INSERT INTO Employees VALUES(3,3,'03/30/2000')
  23: INSERT INTO Employees VALUES(4,0,'08/19/2000')
  24: INSERT INTO Employees VALUES(4,1,'08/20/2000')
  25: INSERT INTO Employees VALUES(4,2,'08/23/2000')
  26: INSERT INTO Employees VALUES(4,3,'08/24/2000')
  27: GO
  28: SELECT *,
  29:      (SELECT 
  30:           CASE WHEN (SUM(CASE WHEN B.CreateDate<C.CreateDate THEN 0 
  31:                ELSE 1 
  32:                END))>=1 THEN 1 
  33:           ELSE 0 
  34:           END AS IsNotProper 
  35:      FROM Employees B CROSS JOIN 
  36:           Employees C
  37:      WHERE B.EmployeID=A.EmployeID 
  38:           AND C.EmployeID=A.EmployeID 
  39:           AND B.Version<>C.Version 
  40:           AND B.Version<C.Version) 
  41:      AS [OutOfOrder] 
  42: FROM Employees A

In the output it will display all the records with a flag called OutOfOrder. If the flag is 1 that means there is a problem with the record order. Otherwise, the EmployeeID’s versions are in proper order.

In the above example you can see that EmployeeID 1 has two records that are out of order.  The 2003-03-18 record came before the 2008-03-17 record, so the query picked these up as being out of order.

Query Result

So, instead of using loops and cursors to determine if there is an issue it handles everything in a single query.