Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-04-05
11
Medium Priority
?
286 Views
Last Modified: 2012-06-27
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:

select ContactID
from ContactHistory
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")

Thanks
0
Comment
Question by:marms767
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 6

Expert Comment

by:chiragkhabaria
ID: 18861512
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)
0
 
LVL 4

Expert Comment

by:opho
ID: 18862136
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
0
 

Author Comment

by:marms767
ID: 18863895
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"
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 4

Expert Comment

by:opho
ID: 18864289
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
0
 

Author Comment

by:marms767
ID: 18864844
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
0
 
LVL 4

Expert Comment

by:opho
ID: 18865183
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
0
 

Author Comment

by:marms767
ID: 18866477
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.
0
 
LVL 4

Expert Comment

by:opho
ID: 18866758
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
0
 
LVL 4

Accepted Solution

by:
opho earned 2000 total points
ID: 18867172
Aggghhhh.... NOW I see where I was off on that.... here's the function, with a change in the code following the COALESCE:

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
GO

--Steve
0
 

Author Comment

by:marms767
ID: 18867256
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
0
 
LVL 4

Expert Comment

by:opho
ID: 18867273
Whew!!!

Glad to hear it's working!

--Steve
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question