Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Compare Dates Expand / Collapse
Author
Message
Posted Wednesday, October 29, 2008 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:14 AM
Points: 140, Visits: 312
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 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 »

Add to briefcase

Permissions Expand / Collapse