Compare Dates

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » Compare Dates

Compare Dates

Rate Topic

Display Mode

Topic Options

Author

Message

Keith DuAime

Posted Wednesday, October 29, 2008 7:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 8:43 AM
Points: 140, Visits: 311

I know this topic has been hit on a lot but came across an article and wanted to get some more input. The article is describing a 'cross join' I put it in quotes because it really isn't and even more interesting when re-written with an inner join the query plan was identicle. Then I wanted to see if I could improve and know for sure many here may even do better and wasn't sure about scalability of either. So the link http://www.sql-server-performance.com/articles/per/Compare_Dates_p1.aspx I re-wrote it with inner join syntax

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

to prove that it is in fact an inner join. Then of course we all want to improve on code Hehe

so I tried this where the execution plan was slightly better.

SELECT PersonRecord.*, COALESCE(NotProperOrderIds.NotInOrder, 0) [Is Not In Proper Order]
FROM PersonRecord
LEFT OUTER JOIN (
	SELECT NotProperOrderRs.PersonId, 1 NotInOrder
	FROM PersonRecord NotProperOrderRs
	WHERE EXISTS(
		SELECT *
		FROM PersonRecord ex
		WHERE
			ex.PersonId = NotProperOrderRs.PersonId AND 
			ex.version > NotProperOrderRs.version AND 
			ex.DEDate < NotProperOrderRs.DEDate
	)
	GROUP BY PersonId
) NotProperOrderIds ON
	NotProperOrderIds.PersonId = PersonRecord.PersonId
GO

and in case no one really reads the article or is lost, the original code from the site:

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.

IF OBJECT_ID('PersonRecord') IS NOT NULL
	DROP TABLE PersonRecord
GO

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 =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 
GO

I was just interested in some feedback and other ideas for solutions. Enjoy!

Post #593609

« Prev Topic | Next Topic »

Permissions