Solved

Find last 10 records by Customer

Posted on 2009-04-07
7
254 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
Comment Utility
What version of SQL Server?
0
 

Author Comment

by:SWRO
Comment Utility
SQL Server 2000
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 22

Expert Comment

by:pivar
Comment Utility
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
Comment Utility
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
Comment Utility
I will take a look at each solution. Thanks.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now