What's wrong with my SQL? (find a "previous" record)

Question

My SQL-Query should return a previous record(Claim). Previous means that it has a different primary key (idData), an equal SSN_Number and an earlier Received_Date. The problem is that the Received_Date could be equal so I have to look for another column. The priority should be the same as the Sort-Order. What am I doing wrong, because the query returns for both ID's the other Record (both claims are the previous for each other)?

My two Test-Records are the following:

The SQL-Query is this:

SELECT TOP (1) Claim.idData     AS ClaimID, 
               PrevClaim.idData AS PrevClaimID 
FROM   tabData AS Claim 
       INNER JOIN tabData AS PrevClaim 
         ON Claim.SSN_Number = PrevClaim.SSN_Number 
            AND Claim.idData <> PrevClaim.idData 
            AND ( Claim.Received_Date > PrevClaim.Received_Date 
                   OR Claim.Claim_Creation_Date > PrevClaim.Claim_Creation_Date 
                   OR Claim.Repair_Completion_Date > 
                      PrevClaim.Repair_Completion_Date 
                   OR Claim.Claim_Submitted_Date > 
                      PrevClaim.Claim_Submitted_Date ) 
WHERE  ( Claim.idData = @claimID ) 
ORDER  BY PrevClaim.Received_Date DESC, 
          PrevClaim.Claim_Creation_Date DESC, 
          PrevClaim.Repair_Completion_Date DESC, 
          PrevClaim.Claim_Submitted_Date DESC 

EDIT: according to Mongus Pong answer this is the correct sql:

SELECT  TOP (1) Claim.idData AS ClaimID, PrevClaim.idData AS PrevClaimID
FROM    tabData AS Claim INNER JOIN tabData AS PrevClaim 
        ON Claim.SSN_Number = PrevClaim.SSN_Number 
        AND Claim.idData <> PrevClaim.idData 
        AND ( Claim.Received_Date > PrevClaim.Received_Date 
         OR Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date > PrevClaim.Claim_Creation_Date 
         OR Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date = PrevClaim.Claim_Creation_Date AND Claim.Repair_Completion_Date > PrevClaim.Repair_Completion_Date
         OR Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date = PrevClaim.Claim_Creation_Date AND Claim.Repair_Completion_Date = PrevClaim.Repair_Completion_Date AND Claim.Claim_Submitted_Date > PrevClaim.Claim_Submitted_Date )
WHERE   ( Claim.idData = @claimID )
ORDER BY PrevClaim.Received_Date          DESC, 
         PrevClaim.Claim_Creation_Date    DESC, 
         PrevClaim.Repair_Completion_Date DESC,
         PrevClaim.Claim_Submitted_Date   DESC

Answer 1

Looks like you get both records because

Claim.Repair_Completion_Date > PrevClaim.Repair_Completion_Date

is true for the second, and the other three clauses in your OR group are true for the first.

You may not wish to compare on Completion Date; perhaps just on one other field, like Claim_Submitted_Date?

Answer 2

The first row has a REPAIR_COMPLETION_DATE that is earlier than the second row's, which means that it will satisfy the JOIN condition Claim.Repair_Completion_Date > PrevClaim.Repair_Completion_Date, thus qualifying as a "previous" claim.

Answer 3

The problem is that you are returning the previous record if any one of those dates are less than the other.

Both records have got at least one date less than the other, so they return eachothers record.

According to what you specify you probably need something like :

Claim.Received_Date > PrevClaim.Received_Date OR
(Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date > PrevClaim.Claim_Creation_Date) OR
(Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date = PrevClaim.Claim_Creation_Date AND Claim.Repair_Completion_Date > PrevClaim.Repair_Completion_Date) OR
(Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date = PrevClaim.Claim_Creation_Date AND Claim.Repair_Completion_Date = PrevClaim.Repair_Completion_Date AND Claim.Claim_Submitted_Date > PrevClaim.Claim_Submitted_Date)

It looks horrendous, but thats SQL for you!