Date: 01/15/06 (SQL Server)    Keywords: sql

table_name
- column_name

utterance
- utteranceID
- speakerID

utteranceHistory
- utteranceHistoryID
- utteranceID
- speakerName
- workTypeDescription
- businessEntityDescription
- eventDate

speaker
- speakerID
- personID

person
- personID
- firstName
- lastName

the task: i need to collect all occurrences when a speakername had been changed in the system. returning in this query: the utteranceID, original speakerName, and final speakerName. I am using MS SQL Server, so i can't use a MINUS as i would have liked to.

-- here is what i tried
-- sadly this is lacking by not returning all of the rows where a speakerName change has occurred
select uh1.utteranceID,
uh1.speakerName AS originalSpearkerName,
uh2.speakerName AS finalSpearkerName
from utteranceHistory uh1 INNER JOIN utteranceHistory uh2
ON (uh1.locationId = uh2.locationId
AND uh1.utteranceID = uh2.utteranceID
AND uh1.utteranceHistoryID <> uh2.utteranceHistoryID)
WHERE uh1.speakerName <> uh2.speakerName
AND uh1.utteranceHistoryID = (SELECT MIN(utteranceHistoryID) FROM
utteranceHistory where utteranceID = uh1.utteranceID)
AND uh2.utteranceHistoryID = (SELECT MAX(utteranceHistoryID) FROM
utteranceHistory utteranceID = uh2.utteranceID)
ORDER BY uh1.utteranceID

-- here is what returns all the changes in the speakerName, however
-- returns two rows per utteranceId, which is really not easy to format
-- especially when the results i am working with are in the thousands.

SELECT utteranceID, speakerName
FROM utteranceHistory
WHERE utteranceID IN
(
SELECT utteranceID
FROM utteranceHistory
WHERE speakerName IS NOT NULL
GROUP BY utteranceid HAVING count(utteranceid) =2
)
and speakerName IS NOT NULL
ORDER BY utteranceid, eventdate

Source: http://community.livejournal.com/sqlserver/39957.html