GROUP BY query with "last two records" criteria (MS SQL Server)
Posted on 2007-04-05
First of all, I am trying to do this query without a loop or cursor.
I have a table called "ContactHistory" in a contact manager DB. It is like the date stamped notes with result codes of the contact with the customer. What I need to know is what contact records in the main table "Contact" have had the latest two records in "ContactHistory" (the key being ContactID) as a particular two result codes. I started by doing this:
where ContactResultID in (5,8)
group by ContactID
having count(*) > 1
This only returns ContactID's at least have one of the two ContactResultID's (result codes which to the user mean things like "Busy/No Answer" or "Bad Phone Number")
What I need is a query that would return contactID's that the last two records are either a 5 or an 8. If say the last three records for a contactID is say a 8 then a 3 then a 5 then this contactID would not qualify.
Fields that I am sure could assist is that ContactHistoryID is auto increment so maybe a "ORDER BY ContactHistoryID DESC" may need to go somewhere or there is an "EnterDate" field on the "ContactHistory" table.
I will use this data to know what contacts I need to remove from a calling list since the last two times we contacted them is was a 5 ("Busy/No Answer") or a 8 ("Bad Phone Number")