Solved

Find last 10 records by Customer

Posted on 2009-04-07
7
297 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:SWRO
[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
7 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24091431
What version of SQL Server?
0
 

Author Comment

by:SWRO
ID: 24092210
SQL Server 2000
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24092271
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:pivar
ID: 24092591
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24093162
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
 

Author Comment

by:SWRO
ID: 24127803
I will take a look at each solution. Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

628 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