Compare Dates

  • 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!

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply