Question: SQL Consecutive records with count

Question

SQL Consecutive records with count

Answers 3
Added at 2017-01-05 18:01
Tags
Question

Table Name Calls

Field 1 - Phone_number
Field 2 - System_outcome

Phone_number      System_outcome  DateTime
--------------------------------------------------
07777778999       Answered        18-12-2016 17:15
07777778123       No Answer       18-12-2016 18:10
07777778999       No Answer       19-12-2016 19:30
07777778999       No Answer       19-12-2016 12:15
07777778999       No Answer       19-12-2016 13:15
07777778999       No Answer       20-12-2016 11:15
07777778124       No Answer       20-12-2016 9:15
07777778128       Answered        20-12-2016 17:15
07777778074       Answered        20-12-2016 17:15

Above is an example, what i need to find out is how do i find numbers that have a consecutive no answer count of more than 6?

at the moment i have been able to get the following but it's not consecutive.

SELECT phone_number,system_outcome,Datetime
FROM calls
WHERE DATE (datetime) BETWEEN '2016-12-23' AND '2016-12-31'
AND system_outcome = 'NO_ANSWER'
GROUP BY phone_number
HAVING count(Phone_number) > 6
Answers
nr: #1 dodano: 2017-01-05 19:01

You can use user defined variables to achieve this.

Try this:

SELECT DISTINCT
    phone_number
FROM
    (SELECT 
        phone_number,
            System_outcome,
            datetime,
            @rn:=CASE
                WHEN
                    @outcome = system_outcome
                        AND @phone = phone_number
                THEN
                    @rn + 1
                ELSE @rn:=1
            END rn,
            @outcome:=system_outcome,
            @phone:=phone_number
    FROM
        (SELECT 
        phone_number, system_outcome, Datetime
    FROM
        calls
    WHERE DATE (str_to_date(datetime,'%d-%m-%Y %H:%i')) BETWEEN '2016-12-01' AND '2016-12-31'
    ORDER BY phone_number, str_to_date(datetime,'%d-%m-%Y %H:%i')) t1
    CROSS JOIN (SELECT @rn:=0, @outcome:='', @phone:='') t2) t
WHERE
    rn >= 4 AND system_outcome = 'No Answer';

@rn variable will be assigned partition-wise (partition by phone_number and system_outcome ordered by datetime) and then filter with row number and type of outcome.

nr: #2 dodano: 2017-01-05 19:01

You can use variables to do this.

select phone_number from (
select c.*,
@prev_outcome:=@cur_outcome,
@cur_outcome:=system_outcome,
@prev_pnum:=@cur_pnum,
@cur_pnum:=phone_number,
case when @cur_pnum = @prev_pnum and @prev_outcome <> @cur_outcome then @rn:=@rn+1
     when @cur_pnum = @prev_pnum and @prev_outcome = @cur_outcome then @rn:=@rn
else @rn:=1 end as rank
from calls c, 
(select @rn:=0,@prev_pnum:='',@cur_pnum:='',@prev_outcome:='',@cur_outcome:='') r
order by phone_number,dt
    ) x
where system_outcome='No Answer'
group by phone_number,rank
having count(*) > 6

This query uses 4 variables

1) @cur_outcome which is initially set to an empty string. Thereafter the select assigns the current row's system_outcome.

2) @prev_outcome which is initially set to an empty string. Thereafter the select sets it to the @cur_outcome (which is an empty string for the first time and so on).

3) @cur_pnum which is initially set to an empty string. Thereafter the select assigns the current row's phone_number.

4) @prev_pnum which is initially set to an empty string. Thereafter the select sets it to @cur_pnum value (which is an empty string initially).

order by clause is important here to designate the current and previous rows based on phone_number and date.

Run the inner query initially to see how the variables are set, which would clarify things for you.

Sample Demo

The demo contains some sample data more than what was shown in the question.

nr: #3 dodano: 2017-01-05 20:01

Here's another variation assuming that calls are ordered in the way they come in

SELECT * FROM (
SELECT  if(System_outcome ='Answered', @con:=0, @con:=@con+1) id, 
    if(Phone_number = @p, @p, @p:=Phone_number) p2,
    CASE
    WHEN Phone_number = @p THEN @con
    ELSE @con:=0 
    END unanswered_count
   ,Phone_number, System_outcome, DateTime
FROM    Table1,
   (SELECT @con:= 0) AS con,
   (SELECT @p:= -1) AS p
) agg
WHERE unanswered_count > 5

EDITED: some variables named wrong

Source Show
◀ Wstecz