Solved

Find last 10 records by Customer

Posted on 2009-04-07
7
293 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 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