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,

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.

Resources:

Comparedates.sql
Total article views: 3705 | Views in the last 30 days: 4
 
Related Articles
FORUM

PersonID with Max(Version) from it...

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

FORUM

Compare Dates

Finding records out of order

ARTICLE

Data Warehouse Version Management

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

FORUM

Insert rows

insert

FORUM

Row Versioning

Row Versioning

Tags
date manipulation    
sql server 7    
t-sql    
 
Contribute

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
Editor, SQLServerCentral.com

Already a member? Jump in:

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