Getting n top rows on a condition

Hi,
Consider a table
Transaction_No (auto sequence no) Unique PK increase factor +1
Customer_Seq bigint
Disposition varchar(20)
Suppose this table is used to keep customer responses. Each time a
customer get called over phone, a unique Trasaction No is generated.
This table keep customer_sequence along with the response in
disposition column. Now I analyzed that there is no use of contacting
those customers who are giving 'Busy' as there Disposition from Last 5
times.
I don't want total count how many time they said that they are Busy.
Instead I need to count if there latest 5 call dispositions are Busy.
So If a customer has been called 25 times, and was busy 23 times, her
current disposition is Busy but in the call before last one her
Disposition was not busy we would count it only one.
How would I get result in format:
Customer_Seq_No Count_of_Last_Consecutive_Busy_Attempts
Thanks
MukeshCan you give the ddl with the sample data and the expected result
and will give you the query :)|||mukesh wrote:
> Hi,
> Consider a table
> Transaction_No (auto sequence no) Unique PK increase factor +1
> Customer_Seq bigint
> Disposition varchar(20)
> Suppose this table is used to keep customer responses. Each time a
> customer get called over phone, a unique Trasaction No is generated.
> This table keep customer_sequence along with the response in
> disposition column. Now I analyzed that there is no use of contacting
> those customers who are giving 'Busy' as there Disposition from Last 5
> times.
> I don't want total count how many time they said that they are Busy.
> Instead I need to count if there latest 5 call dispositions are Busy.
> So If a customer has been called 25 times, and was busy 23 times, her
> current disposition is Busy but in the call before last one her
> Disposition was not busy we would count it only one.
> How would I get result in format:
> Customer_Seq_No Count_of_Last_Consecutive_Busy_Attempts
> Thanks
> Mukesh
Here's my guess together with some test data. I've assumed you are
using SQL Server 2005.
CREATE TABLE Calls (Transaction_No INTEGER NOT NULL PRIMARY KEY,
Customer_Seq BIGINT NOT NULL, Disposition VARCHAR(20));
INSERT INTO Calls (Transaction_No, Customer_Seq, Disposition)
SELECT 1, 1, 'Busy' UNION ALL
SELECT 2, 1, 'Not Busy' UNION ALL
SELECT 3, 1, 'Busy' UNION ALL
SELECT 4, 2, 'Not Busy' UNION ALL
SELECT 5, 2, 'Busy' UNION ALL
SELECT 6, 2, 'Busy' UNION ALL
SELECT 7, 2, 'Busy' ;
WITH T AS
(SELECT Customer_Seq, Disposition,
ROW_NUMBER() OVER (PARTITION BY Customer_Seq
ORDER BY Transaction_No DESC)-1 AS cnt
FROM Calls)
SELECT Customer_Seq,
COALESCE(MIN(CASE WHEN Disposition<>'Busy'
THEN cnt END),COUNT(*))
AS Count_of_Last_Consecutive_Busy_Attempts
FROM T
GROUP BY Customer_Seq ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This is the solution in SQL Server 2000. But there is a catch.
It depends on what David understood from your question and what I understood
from his answer :)
Anyways, I am using the same table structure (thank you David, you are a
sweetheart)
Now the result gives you the last consecutive count of busy or not busy
disposition for each cutomer. If you want one the customers who were busy at
least the last time, then uncooment the condition in the where clause.
Hope this helps. Let me know..
CREATE TABLE Calls (Transaction_No INTEGER NOT NULL PRIMARY KEY,
Customer_Seq BIGINT NOT NULL, Disposition VARCHAR(20));
INSERT INTO Calls (Transaction_No, Customer_Seq, Disposition)
SELECT 1, 1, 'Busy' UNION ALL
SELECT 2, 1, 'Busy' UNION ALL
SELECT 3, 1, 'Busy' UNION ALL
SELECT 4, 2, 'Not Busy' UNION ALL
SELECT 5, 2, 'Busy' UNION ALL
SELECT 6, 2, 'Busy' UNION ALL
SELECT 7, 2, 'Busy' UNION ALL
SELECT 9, 1, 'Busy' UNION ALL
SELECT 10, 1, 'Busy' UNION ALL
SELECT 11, 1, 'Not Busy' UNION ALL
SELECT 12, 2, 'Not Busy' UNION ALL
SELECT 13, 2, 'Busy' UNION ALL
SELECT 14, 2, 'Busy' UNION ALL
SELECT 15, 2, 'Busy' UNION ALL
SELECT 16, 3, 'Busy' UNION ALL
SELECT 17 , 3, 'Busy' UNION ALL
SELECT 18, 3, 'Busy' ;
select customer_seq, count(*), a.disposition
from
(
select a.Customer_Seq , a.disposition, count(*) as Customer_call_Seq
,sum(case when a.disposition = b.disposition then 1 else 0 end) as
Screwed_up_numbering from calls a , calls b
where a.customer_seq = b.customer_seq
and a.transaction_No >= b.transaction_No
group by a.Transaction_No, a.Customer_Seq, a.disposition
) as a
where
--disposition = 'busy' and
Customer_call_Seq = Screwed_up_numbering
group by customer_seq,disposition|||And if you are bothered only about the last 5 calls,
then use this query.
select customer_seq, count(*), a.disposition
from
(
select a.Customer_Seq , a.disposition, count(*) as Customer_call_Seq
,sum(case when a.disposition = b.disposition then 1 else 0 end) as
Screwed_up_numbering from calls a , calls b
where a.customer_seq = b.customer_seq
and a.transaction_No >= b.transaction_No
group by a.Transaction_No, a.Customer_Seq, a.disposition
having count(*) <= 5
) as a
where
--disposition = 'busy' and
Customer_call_Seq = Screwed_up_numbering
group by customer_seq,disposition