InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 19:09:49 GMT20http://www.sqlservercentral.com/Forums/Topic593609-65-1.aspxI 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 [url]http://www.sql-server-performance.com/articles/per/Compare_Dates_p1.aspx[/url] I re-wrote it with inner join syntax [code]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[/code] 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.[code]SELECT PersonRecord.*, COALESCE(NotProperOrderIds.NotInOrder, 0) [Is Not In Proper Order]FROM PersonRecordLEFT 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.PersonIdGO[/code]and in case no one really reads the article or is lost, the original code from the site:[quote]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.[/quote][code]IF OBJECT_ID('PersonRecord') IS NOT NULL DROP TABLE PersonRecordGOCREATE TABLE PersonRecord ( PersonID int, Version int,DEDate datetime)goINSERT 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') goSELECT *,(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[/code]I was just interested in some feedback and other ideas for solutions. Enjoy!Wed, 29 Oct 2008 07:45:34 GMTKeith DuAime