Compare Dates in an iterative manner for a particular set of records

  • Divya Agrawal

    Hall of Fame

    Points: 3144

    Comments posted to this topic are about the item Compare Dates in an iterative manner for a particular set of records

    --Divya

  • sharmago

    Valued Member

    Points: 60

    Hi Divya, it gud..

    you just can minimize the query length a little by removing the highlighted text...

    SELECT *,

    (SELECT CASE

    WHEN (SUM(CASE WHEN B.DEDate =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

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice one Divya. 🙂

  • sharmago

    Valued Member

    Points: 60

    can anyone perform this task with two tables only....

  • Divya Agrawal

    Hall of Fame

    Points: 3144

    Yes it will optimize the performance of the query

    --Divya

  • Divya Agrawal

    Hall of Fame

    Points: 3144

    Thanks

    --Divya

  • Divya Agrawal

    Hall of Fame

    Points: 3144

    Performing task with two tables??

    Could you please clarify your query??

    --Divya

  • James Goodwin

    Hall of Fame

    Points: 3541

    Why use the cross join?

    SELECT *

    FROM PersonRecord A

    INNER JOIN PersonRecord B

    ON A.PersonID = B.PersonID

    AND A.VersionID = B.VersionID - 1

    WHERE B.DEDate > A.DEDate

  • sharmago

    Valued Member

    Points: 60

    yes the task could have been done this way but she wants all the records to be called improper if any of the date is not proper

  • SqlAsSecondLanguage

    Mr or Mrs. 500

    Points: 529

    Nice ideas - I work in the insurance industry and use this often to look for out-of-sequence endorsements and transactions. Here's another way to acheive it, without a join.

    select personid, version, dedate,

    row_number() over (partition by personid order by version) as VersionOrder,

    row_number() over (partition by personid order by dedate) as DeOrder,

    case when row_number() over (partition by personid order by version) -

    row_number() over (partition by personid order by dedate) <> 0 then '1' else '0' end as IsImproper

    from personrecord

    order by personid, version

    Only catch here (and for others) is that both fileds you are comparing over a given personid must be unique...

  • ccz99

    Old Hand

    Points: 360

    Here is a solution not using CROSS JOIN:

    SELECT A.*,

    CASE WHEN EXISTS (

    SELECT 1

    FROM (

    SELECT B.*,

    (SELECT TOP 1 C.DEDate

    FROM PersonRecord C

    WHERE C.PersonID = B.PersonID AND C.VERSION > B.VERSION

    ORDER BY C.Version) AS [NextDEDate]

    FROM PersonRecord B

    ) AS [BC]

    WHERE BC.PersonID = A.PersonID AND BC.NextDEDate < DEDate

    )

    THEN 1 ELSE 0 END AS [Is ImProper]

    FROM PersonRecord A

  • gerhard-356547

    SSC Rookie

    Points: 41

    Not sure if this is what you are trying to do but how about:

    SELECT

    pr1.PersonID,pr1.Version,pr1.DEDate,

    CASE

    WHEN (pr1.DEDate < pr2.DEDate)

    THEN 1

    ELSE

    0

    END AS Improper

    FROM

    PersonRecord pr1

    LEFT JOIN PersonRecord pr2

    ON

    pr1.PersonID = pr2.PersonID

    AND

    pr1.Version = pr2.Version + 1

  • Adam Haines

    SSC-Insane

    Points: 23197

    Good article!

    This type of query can be handled much easier in SQL 2005, but I would still try to avoid a correlated subquery in SQL 2000. You can move the query into a derived table and make it inline. Obviously, this is over simplifying the solution but you get the idea.

    SELECT

    PersonRecord.*,

    Improper.[IsImProper]

    FROM PersonRecord

    INNER JOIN(

    SELECT

    a.PersonID,

    MAX(CASE

    WHEN a.Version < b.version and A.DEDate > B.DEDate

    THEN 1

    ELSE 0

    END) as IsImProper

    FROM PersonRecord A

    INNER JOIN PersonRecord B

    ON A.PersonID = B.PersonID AND

    A.Version = B.Version -1

    GROUP BY A.PersonId

    ) AS Improper

    ON Improper.[PersonID] = PersonRecord.PersonID

  • Divya Agrawal

    Hall of Fame

    Points: 3144

    Reply to :Why use the cross join?

    Yes it could be done in a way you have shown but then the whole personid should be marked as improper if any DEDATE is found mismatched

    --Divya

  • Divya Agrawal

    Hall of Fame

    Points: 3144

    select personid, version, dedate,

    row_number() over (partition by personid order by version) as VersionOrder,

    row_number() over (partition by personid order by dedate) as DeOrder,

    case when row_number() over (partition by personid order by version) -

    row_number() over (partition by personid order by dedate) <> 0 then '1' else '0' end as IsImproper

    from personrecord

    order by personid, version

    Yes its good but this was an option if you are using SQL Server 2005. My problem was to be solved in SQL Server 2000

    --Divya

Viewing 15 posts - 1 through 15 (of 42 total)

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