What happened to my SQL? (find the "previous" entry)

Question

My SQL query should return the previous record (Claim). The previous one means that it has another primary key (idData) equal to SSN_Number and an earlier Received_Date. The problem is that Received_Date may be equal, so I need to look for another column. Priority should be the same as Sort-Order. What am I doing wrong because the query returns a different record for both IDs (both statements are previous to each other)?

My two test entries: the same SSN_Number http://www.bilder-hochladen.net/files/4709-kr.jpg

SQL Query:

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's answer, this is the correct code:

 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

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

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

According to what you have specified, you will 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 awful, but for you it's SQL!

Answer 2

It looks like you are getting both records because

 Claim.Repair_Completion_Date > PrevClaim.Repair_Completion_Date 

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

You may not want to compare the completion date; perhaps only in another field, for example Claim_Submitted_Date?

Answer 3

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