Sql count / sum the number of calls up to a certain date in another column

Question

I have data that shows customer calls. I have columns for customer number, phone number (one client can have many), recording dates for each voice call, and call duration. The table looks below an example.

CusID |  PhoneNum   |  Date             | Duration
20111    43576233   20.01.2016-14:00     00:10:12
20111    44498228   14.01.2016-15:30     00:05:12
20112    43898983   14.01.2016-15:30     

I want to count the number of call attempts for each number before answering (Duration is> 0). So that I can estimate how many times I have to call on average to contact a customer or phone number. It should basically read any column on the phone number to min (Date), where the duration is → 0.

SELECT Phone, Min(Date) FROM XX WHERE Duration IS NOT NULL GROUP BY Phone -- 

I think this should give me a time limit until I count the number of calls. I could not figure out how to finish the rest of the work.

EDIT- I will add an example

5, , . :

Answer 1

:

    SELECT
        CusID
        ,PhoneNum
        ,MIN(Date) AS MinDate
    FROM XX
    WHERE Duration IS NOT NULL
    GROUP BY CusID, PhoneNum

PhoneNum .

, ( PhoneNum). PhoneNum . LEFT JOIN, , .

WITH
CTE
AS
(
    SELECT
        CusID
        ,PhoneNum
        ,MIN(Date) AS MinDate
    FROM XX
    WHERE Duration IS NOT NULL
    GROUP BY CusID, PhoneNum
)
SELECT
    CusID
    ,PhoneNum
    ,COUNT(XX.PhoneNum) AS Count
FROM
    CTE
    LEFT JOIN XX
        ON  XX.PhoneNum = CTE.PhoneNum
        AND XX.Date < CTE.MinDate
GROUP BY CusID, PhoneNum
;

, .

Answer 2

:

SELECT phonecalls.CusID, COUNT(0) AS failedcalls, phonenumber, success.firstsuccess FROM phonecalls, 
(SELECT min(Date) AS firstsuccess, CusID, phonenumber FROM phonecalls WHERE Duration IS NOT NULL GROUP BY CusID, phonenumber) success
WHERE phonecalls.CusID = success.CusID AND phonecalls.phonenumber = success.phonenumber AND phonecalls.Date < success.firstsuccess
GROUP BY phonecalls.CusID, phonecalls.phonenumber, success.firstsuccess;

...

. , , . , ? , " ":

    SELECT phonecalls.CusID, COUNT(0) AS failedcalls, phonenumber, success.firstsuccess FROM phonecalls LEFT JOIN 
(SELECT min(Date) AS firstsuccess, CusID, phonenumber FROM phonecalls WHERE Duration IS NOT NULL GROUP BY CusID, phonenumber) success ON 
phonecalls.CusID = success.CusID AND phonecalls.phonenumber = success.phonenumber AND phonecalls.Date < success.firstsuccess
GROUP BY phonecalls.CusID, phonecalls.phonenumber, success.firstsuccess;

Answer 3

SQL Server 2012+ :

• "" . .
• .
• .
• .
• , .

:

select phone, max(cume_unanswered), count(*) as num_answered,
       max(cume_unanswered) * 1.0 / count(*) as ratio
from (select t.*,
             sum(case when duration is null then 1 else 0 end) over (partition by phone order by date) as cume_unanswered
      from t
     ) t
where duration is not null
group by phone;