GROUP BY query with "last two records" criteria (MS SQL Server)

Somthing like this ??

Select * From ContactHistory c
Where c.ContactHistoryID In
(
      Select  Top 2 ContactHistoryID  From ContactHistory c1
      Where C1.ContactID = C.ContactID
      ContactID Exists  
      (
            select 1
            from ContactHistory c2
            where ContactResultID in (5,8) and C1.ContactID = C2.ContactID
            group by ContactID
            having count(*) > 1      )
      order by 1 Desc
)
and ContactResultID in (5,8)

So, if you were doing this manually, you're saying you would:

  * Find each  contact's two most recent history records
  * Add each contact to the list if both of their ResultIDs are either an 8 or a 5

One way to get there would be to create a function which collects the last two result IDs for any contact ID, and then use the function in your select.

Here's the function:

CREATE FUNCTION GetRecentHistoryForContact  @ContactID int)
RETURNS varchar(8)
AS
BEGIN

  DECLARE @ResultIDs varchar(8)

  select top 3
    @ResultIDs = coalesce(@ResultIDs+cast(ContactResultID as varchar(4))+ ',','')
    from dbo.ContactHistory
   where ContactID = @ContactID
   order by ContactDate desc
  set @ResultIDs = left(@ResultIDs,3)
  RETURN @ResultIDs

END
GO

And then, the select statement is:

select
  a.ContactID,
  GetRecentHistoryForContact(a.ContactID)
from
  (select distinct ContactID
     from dbo.ContactHistory
    where ContactResultID in (5,8)) a
where
  GetRecentHistoryForContact(a.ContactID) in ('5,8','8,5','5,5','8,8')

--Steve

I spent about 30 minutes trying to get either solution to work. The first one I did not get anywhere with.
The second I don't see the function working. I tested the function by itself entering single contactID's that I knew for sure had the first two records being a 5 or an 8 and the function would only return the first record with a comma like this "8," instead of the expected "8,5"

I was getting only the "8," when I first had the function set to select "top 2" instead of "top 3".

I set up a test table with the fields as indicated, and the function and query were working.

If you want to post your function code, we could probably figure out what's hanging things up.

--Steve

CREATE  FUNCTION GetRecentHistoryForContact (@ContactID int)
RETURNS varchar(8)
AS
BEGIN

  DECLARE @ResultIDs varchar(8)

  select top 3
    @ResultIDs = coalesce(@ResultIDs+cast(ContactResultID as varchar(4))+ ',','')
    from dbo.ContactHistory
   where ContactID = @ContactID
   order by HistoryDate desc
  set @ResultIDs = left(@ResultIDs,3)
  RETURN @ResultIDs

END

GO

Nothing's jumping out at me there... so, the debugging steps worth trying include:

  * confirm your ContactID and ContactResultID fields are defined as an integers
  * open up the function to return more results by changing all
        varchar(8)   to   varchar(20)
        top 3    to top 10
        delete  line set @ResultIDs = left(@ResultIDs,3)
  * drop the where clause from the query

That should give a list of all contactIDs which have at least 1 value of 5 or 8 in the ContactResultID at any time, along with a list of their ContactResultIDs in reverse chronological order.

For any of the ContactIDs that show up in the query result, you should be able to run a query like

select ContactResultID, Historydate from dbo.ContactHistory
   where ContactID = [the contact ID from the query result]
   order by HistoryDate desc

and see the resultIDs showing up in the same order.

Sorry if this is painfully obvious.... this has just got to be something simple staring us in the face.

--Steve

I tried all that and here is my findings.

The function knocks off the last value every time.

Say if contactID has three contactHistory records then it will return the first two i.e. "5,8",but if the ContactID has only two records it only returns the first one i.e. "5,"

The same type of thing occurs if I take off the where clause and change "top 3" to say "top 5" then it will return 4 values followed by the last comma i.e. "5,3,6,3,".

So if we can figure out how to get that last value in the list I think this deal will work.

One other thing occurs to me:  Is it possible for the HistoryDate or ContactResultID values to be Null?  

If you have one record with Null in the ContactResultID, based on my test results, it will not be included in the output of the function.

If you have one record with Null in both the ContactResultID and the HistoryDate columns, you'll get an empty string as a result out of the function even if there is other valid data for that Contact ID.

That makes my version of the function look like this:

ALTER FUNCTION dbo.GetRecentHistoryForContact
      (@ContactID int)
RETURNS varchar(20)
AS
BEGIN

      DECLARE @ResultIDs varchar(20)

  select top 10
    @ResultIDs = coalesce(@ResultIDs+cast(ContactResultID as varchar(4))+ ',','')
    from dbo.ContactHistory
   where ContactID = @ContactID
     and not ContactResultID is Null
     and not ContactDate is Null
   order by ContactDate desc

      RETURN @ResultIDs

END

and my query for seeing all of the results look like:

select
  a.ContactID,
  dbo.GetRecentHistoryForContact(a.ContactID)
from
  (select distinct ContactID
     from dbo.ContactHistory
    where ContactResultID in (5,8)) a
-- where
--   dbo.GetRecentHistoryForContact(a.ContactID) in ('5,8','8,5','5,5','8,8')

The table on which I'm testing is defined:

CREATE TABLE [ContactHistory] (
      [ContactHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
      [ContactID] [int] NULL ,
      [ContactResultID] [int] NULL ,
      [ContactDate] [datetime] NULL ,
      CONSTRAINT [PK_ContactHistory] PRIMARY KEY  CLUSTERED
      (
            [ContactHistoryID]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO

And, the data from which I got the results I expected contains:

ContactHistoryID ContactID ContactResultID ContactDate
      8      5      9      1/18/2007
      12      5      8      1/12/2007
      6      5      7      1/10/2007
      10      5      9      1/8/2007
      3      5      6      1/7/2007
      4      5      3      1/5/2007
      16      6      4      2/12/2007
      15      6      2      2/10/2007
      17      6      5      2/9/2007
      14      6      8      2/8/2007
      9      6      2      2/6/2007
      11      6      6      2/5/2007
      1      6      10      2/3/2007
      13      6      5      2/1/2007
      18      7      5      3/10/2007
      19      7      4      3/9/2007
      20      7      8      3/8/2007
      21      7      5      3/7/2007
      22      7      4      3/6/2007
      23      7      8      3/5/2007
      24      7      5      3/4/2007
      25      7      4      3/3/2007
      26      7      8      3/2/2007
      27      7      9      3/1/2007
      7      8      8      1/13/2007
      5      8      5      1/9/2007
      2      8      8      1/6/2007
      28      5      <null>       <null>

--Steve

Steve,

I came up with something that worked using your same idea and got the function to work with this. I completed this before I check your email for your last post.

ALTER  FUNCTION GetRecentHistoryForContact (@ContactID int)
RETURNS varchar(5)
AS
BEGIN
        DECLARE @ResultIDs varchar(5)
      DECLARE @NextResultID varchar(2)
      DECLARE @LastContactHistoryID int
      
      select top 1 @NextResultID = cast(ContactResultID as varchar(2)), @LastContactHistoryID = ContactHistoryID
      from ContactHistory
      WHERE ContactID = @ContactID
      ORDER BY ContactHistoryID DESC
      
      set @ResultIDs = @NextResultID
      set @NextResultID = 0
      
      select top 1 @NextResultID = cast(ContactResultID as varchar(2))
      from ContactHistory
      WHERE ContactID = @ContactID
      AND ContactHistoryID <> @LastContactHistoryID
      ORDER BY ContactHistoryID DESC
      
      set @ResultIDs = @ResultIDs + ',' + @NextResultID
 
      RETURN @ResultIDs
END
GO

I tried your last post and it did work as well (faster than my work around attempt)

Thanks

Whew!!!

Glad to hear it's working!

--Steve