Compare Dates

Introduction
This article is nothing new, but well it is worth revising for those who have not used CROSS JOINS. Many of ypu might have faced a problem while dealing with comparisons on a particular set of records. This could be a comparison 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, 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 was to compare on DEDate’s of various versions of a particular PersonID in such a way that the succeeding version’s DEDate should be greater than its preceding version’s DEDate. If it is so, then that PersonID’s version is said to be in a proper order, otherwise it is said to as in an Awkward order.
 
Solution
I have tried lots of ways. Using a cursor is the one of the solutions. 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 the CROSS JOINS on the table for the same PersonID to get the Cartesion Product of the Versions. Then I 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

All the other combinations of Cartesian product are neglected. So, in all if it returns false in any of the case, the PersonID versions is said to in an improper order.

Output
In the output it will display all the records with a flag called Is Im Proper. If the flag is 1 that means there is a problem with the record. Otherwise, the PersonID versions are in a proper order.
So, instead of using loops and cursors it handles everything in a single query.

Script
The following script illustrates this solution:

CREATE TABLE PersonRecord ( PersonID int, Version int,DEDate datetime)
go
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')

go
SELECT *,(SELECT CASE WHEN (SUM(CASE WHEN B.DEDate<C.DEDate THEN 0 ELSE 1 END))>=1
   THEN 1 ELSE 0 END AS IsnotProper from PersonRecord B CROSS JOIN  PersonRecord C
   WHERE B.PersonID=A.PersonID AND C.PersonID=A.PersonID AND B.VERSION<>C.VERSION
   AND B.VERSION<C.VERSION) AS [Is Not In Proper Order]
from PersonRecord A