Latest Date with Inner Join Query

rstelma · November 4th, 2003, 12:31 PM

Thank you for your help.

I have a CRM application with a Call Log table and Incidents table with a one to many relationship, multiple incidents to a call log. The call log holds the status(SolutionCode)of the last incident.

I've been getting some errors in the application where the status in the call log does not have the correct status from the latest incident. To track down this error I want to pull the latest incident for a call log where the SolutionCode in the incident is not equal to the Status in the call log.

Status Solution Code RecordDate

CallLog5 Resolved
Incident1 Solution Provided 11/1/2003
Incident2 Resolved 10/24/2003
Incident3 Non Supported 10/10/2003

These are keyed with a column called TheCaseID in both tables. I want to be able to pull CallLog5 since the SolutionCode of the latest incident is not reflected in the status column for CallLog5. CallLog5 should have a status of Solution Provided and not Resolved.

I would also like to use this query as a sub query to correct the errors in the database without having to reset all statuses.

SELECT TheCase.Status, Incident.SolutionCode, TheCase.TheCaseID, Incident.RecordDate
FROM TheCase INNER JOIN Incident ON
TheCase.TheCaseID = Incident.TheCaseID
Where(the solutioncode for the latest incident <> the status in the call log.)

I have a solution now which is to pull all of the records, order by RecordDate Desc, looping through all of the records and testing only the first record in a set for Status vs. SolutionCode. Not very elegant and very resource intensive.

Thanks for your help.

Richard Stelma

Jeff Mason · November 4th, 2003, 04:49 PM

You'll need to use a correlated subquery.

The JOIN query you posted is a good starting point:

Code:

SELECT  TheCase.Status, Incident.SolutionCode, TheCase.TheCaseID, Incident.RecordDate
FROM TheCase INNER JOIN Incident ON 
TheCase.TheCaseID = Incident.TheCaseID

The resultset from this will list all the rows for all TheCases with all their incidents.

We just need to add WHERE clauses to select only the rows we want from that resultset. There are two conditions for these rows. The first is we are only interested in rows where RecordDate is a maximum for a given TheCaseID. The second is that of those, we are only interested in rows where TheCase.Status is not equal to Incident.SolutionCode.

Of course, the second WHERE clause is trivial, and we'll just AND the two clauses together to only select rows which satisfy both conditions.

To find the row with the maximum RecordDate for a given TheCaseID, we would construct a query like the following. For now, let's assume the TheCaseID we are interested in is a parameter. The query would be

Code:

SELECT MAX(RecordDate) FROM Incident WHERE TheCaseID=@parameter

Simple enough.

What we want, though, is to execute this query for each value of TheCaseID, in effect substituting each value for the parameter. This is where a correlated subquery comes in. This kind of query is in effect executed for every row of the outer, containing query. We do this by setting up a WHERE clause in the subquery which relates to the TheCaseID in the outer query. Since both queries refer to the same table, we'll need to assign an alias (or correlation name) to the table so each query will know which value it is referring to:

Code:

SELECT  TheCase.Status, I1.SolutionCode, TheCase.TheCaseID, I1.RecordDate
FROM TheCase INNER JOIN Incident I1 ON 
TheCase.TheCaseID = I1.TheCaseID
WHERE I1.RecordDate=
    (SELECT MAX(RecordDate) FROM Incident I2 WHERE I1.TheCaseID=I2.TheCaseID)
    AND TheCase.Status<>I1.SolutionCode;

should do the trick.

It's tough to refrain from pointing out that had your tables been properly normalized so that the Status value was not duplicating the SolutionCode, you wouldn't have had this problem in the first place. ;)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

rstelma · November 5th, 2003, 02:09 AM

Hi Jeff,

You are amazing. Thank you.

I won't be at work for a day or so but it looks good to me.

Please don't hold back on your comments concerning normaliztion.

Just a quick question on this issue.

So, to normalize these tables properly and to pull the latest status for a CallLog would you just query for the SolutionCode of the latest Incident, somewhat like we did here, and then just do away with the status in the CallLog table?

Thanks,
Richard

Jeff Mason · November 5th, 2003, 07:32 AM

Quote:

quote:So, to normalize these tables properly and to pull the latest status for a CallLog would you just query for the SolutionCode of the latest Incident, somewhat like we did here, and then just do away with the status in the CallLog table?

Exactly right.

I like the following aphorism as a definition of normalization:

One fact, one time, one place, one way.

You had the same fact represented in two places, and, not unsurprisingly, they got out of sync with one another, leaving you with a bit of a mess.

There are many times when it is appropriate to denormalize a table or two. But normalize the data correctly first, then denormalize where it makes sense. Just be sure when you do that you do so fully conscious of what can go wrong.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com