Solved

Find last 10 records by Customer

Posted on 2009-04-07
7
288 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
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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 needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

685 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