INTRODUCTION

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.

Solution

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)
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 isawk 
 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 ImProper] 
from PersonRecord A

OUTPUT

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.