Find last 10 records by Customer

Basic setup..

Activities_Table (
ID INT NOT NULL IDENTITY(1,1) PRIMARY_KEY
,CustomerID INT
, Contacted TINYINT --(1 or 0)
, ContactDate SMALLDATETIME
);

Every time there is an attempt to contact a customer an entry is logged that shows the CustomerID, whether or not the customer was actually contacted (1 oir 0) and the date of the contact attempt.

Given that, I am trying to do the following:
Exclude (from a larger resutlset) anyone that has been unsuccessfully contacted 10 times in a row since 3/1/2009.

So, something like...
SELECT lots of Customer data FROM some different tables (that have CustomerID as a FK)
WHERE NOT EXISTS (or NOT IN) -- Code the Exclusion Here

This one is stumping me.

Thanks in advance!
The
SWROAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should do it:
SELECT t.*
  FROM Activities_Table t
 WHERE t.ContactDate  IN ( SELECT TOP 10 i.ContactDate  
                             FROM Activities_Table i
                             WHERE i.CustomerID = t.CustomerID
                            ORDER BY i.ContactDate DESC
                           ) 

Open in new window

0
 
BrandonGalderisiCommented:
What version of SQL Server?
0
 
SWROAuthor Commented:
SQL Server 2000
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
pivarCommented:
Hi,

I believe this is what you looking for. I assume you stop calling after a successful attempt.

/peter
select *
from Customer_Table ct
where 10 > 
  (select count(at.CustomerID) from Activities_Table at 
    where at.CustomerID=ct.CustomerID and at.ContactDate >= '20090103' and  
     at.Contacted=0)

Open in new window

0
 
BrandonGalderisiCommented:
Ok.  I think I have what you want.

So for each customer contact record, the CNT represents the number of the 10 subsequent records where contacted = 1.  So if you have 0, that means that record PLUS the 9 following it are all contacted=0.  1 means that 1 of the next 9 (or itself) have a value of 1 for contacted.  etc.

So the select statement returns everything so that you can see.  Pay attention to column CNT.  If the value is 0 then you have 10 consecutive records without being contacted.  If you remove the --comment from the line "--where cnt=0" then it will return you the FIRST record from any customer who has 10 consecutive contacted=0.  

If there are 11,12,13,etc.  in a row, then all of them will show up.  But I imagine based on your requirements that only the existence of one matters to you.


Here is the sample data I generated, plus the query.
set nocount on
go
create table #Activities_Table 
(ID            INT NOT NULL IDENTITY(1,1) PRIMARY KEY
,CustomerID    INT
,Contacted    TINYINT --(1 or 0)
,ContactDate SMALLDATETIME
);
go
declare @i int,@d datetime,@c int,@cl tinyint
set @d=getdate()
set @cl=0
 
while @cl<=1
begin
     set @c=0
     while @c<10
     begin
     set @i=0
          while @i<10
          begin
               set @d=@d+1
               insert into #Activities_Table (customerid,contacted,contactdate)
               values(@c,@cl,@d)
          set @i=@i+1
          end
     set @c=@c+1
     end
set @cl=@cl+1
end
goselect * from (
select *, (select sum(contacted) 
                    from #activities_table at2
                    where customerid=at.customerid 
                      and contactdate in (select top (10) contactdate 
                                          from #activities_table
                                          where customerid = at2.customerid
                                            and contactdate >= at.contactdate
                                          order by contactdate)
                   ) cnt
from #Activities_Table at
)a
--where cnt=0
order by customerid,contactdate
go
drop table #Activities_Table 
go

Open in new window

0
 
SWROAuthor Commented:
I will take a look at each solution. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.