Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Compare Dates in an iterative manner for a particular set of records

By Divya Agrawal,


This article is nothing new, but well it is worth revising for those who have not used CROSS JOINS. Many of them might have faced a problem while dealing with comparisons on a particular set of records. This could be comparisons of Dates or Numbers or anything, but between a specific set of records.

Let's say I have table in which there are three fields:
1) PersonID
2) Version
3) DEDate

Here, PersonID is an integer field and could be considered as a primary ID. Version is again an integer field and could be considered a secondary ID. So, in all PersonID and Version will compositely form the Composite Key. There can be many versions of the same PersonID and each Version would have a Data Entry Date (DEDate). Our agenda is to compare the Data Entry Dates of all the versions of a particular person using a Single Query in SQL Server 2000.

Our Agenda is to compare on DEDate's of various versions of a particular personid in such a way that the succeeding row's DEDate should be greater than its preceding row's DEDate. If it is so, then that PersonID's row is said to be in a proper order, otherwise it is said to as in an awkward order.


I have tried lots of ways. Using cursor is the one of the solution. But, as the number of records are high cursor should be avoided. I have then tried using inner joins, subqueries.
But the best solution I found is by using CROSS JOINS.

What I have done first is considered a single PersonID. Then I have applied Cross joins on the table for the same PersonID to get the Cartesian Product of the Versions. Then just have made the comparisons in the following way:

  1. Zeroth version's DEDate is compared to be smaller than First version's DEDate
  2. Zeroth version's DEDate will be compared to be smaller than Second version's DEDate
  3. First version's DEDate is compared to be smaller than Second version's DEDate

So, in all if it returns false in any of the case, the personid's versions is said to in an improper order. The following Script will help you understanding the problem and its solution in a better way.

			SELECT PersonID,Version,DEDate from PersonRecord
			CREATE TABLE PersonRecord ( PersonID int, Version int,DEDate datetime)
INSERT INTO PersonRecord VALUES(1,0,'03/10/2000')
INSERT INTO PersonRecord VALUES(1,1,'03/16/2000')
INSERT INTO PersonRecord VALUES(1,2,'03/19/2000')
INSERT INTO PersonRecord VALUES(1,3,'03/18/2000')
INSERT INTO PersonRecord VALUES(1,4,'03/17/2000')
INSERT INTO PersonRecord VALUES(2,0,'02/10/2000')
INSERT INTO PersonRecord VALUES(2,1,'02/11/2000')
INSERT INTO PersonRecord VALUES(2,2,'02/18/2000')
INSERT INTO PersonRecord VALUES(3,0,'03/25/2000')
INSERT INTO PersonRecord VALUES(3,1,'03/23/2000')
INSERT INTO PersonRecord VALUES(3,2,'03/26/2000')
INSERT INTO PersonRecord VALUES(3,3,'03/30/2000')
INSERT INTO PersonRecord VALUES(4,0,'08/19/2000')
INSERT INTO PersonRecord VALUES(4,1,'08/20/2000')
INSERT INTO PersonRecord VALUES(4,2,'08/24/2000')
INSERT INTO PersonRecord VALUES(4,3,'08/23/2000')
       (SELECT CASE 
          WHEN (SUM(CASE WHEN B.DEDate<C.DEDate THEN 0 ELSE 1 END))>=1 
               THEN 1 ELSE 0 
        END AS isawk 
 from PersonRecord B CROSS JOIN PersonRecord C
 WHERE B.PersonID=A.PersonID 
 AND C.PersonID=A.PersonID 
from PersonRecord A


This article is for people facing a similar problem in day to day use. If anyone has any other suggestions, please feel free to add them to the discussion for this article.


Total article views: 3710 | Views in the last 30 days: 3
Related Articles

PersonID with Max(Version) from it...

Hi, how can I get effectively max(version) to identify any PersonID with multiple VersionID? [u]Pe...


Compare Dates

Finding records out of order


Data Warehouse Version Management

In most tables it is enough to have a stored procedure that performs Update Else Insert logic for da...


Insert rows



Row Versioning

Row Versioning

date manipulation    
sql server 7    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones