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?

[Webinar] Streamline your web hosting managementRegister Today

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
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.

 
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
All Courses

From novice to tech pro — start learning today.