Solved

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

Posted on 2007-04-05
11
272 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
  • 6
  • 4
11 Comments
 
LVL 6

Expert Comment

by:chiragkhabaria
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 4

Expert Comment

by:opho
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 4

Expert Comment

by:opho
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Whew!!!

Glad to hear it's working!

--Steve
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now