Reputation: 342

Caller whose first and last call was to the same person

I have a phonelog table that has information about callers' call history. I'd like to find out callers whose first and last call was to the same person on a given day.

Callerid  Recipientid        DateCalled
1          2            2019-01-01 09:00:00.000
1          3            2019-01-01 17:00:00.000
1          4            2019-01-01 23:00:00.000
2          5            2019-07-05 09:00:00.000
2          5            2019-07-05 17:00:00.000
2          3            2019-07-05 23:00:00.000
2          5            2019-07-06 17:00:00.000
2          3            2019-08-01 09:00:00.000
2          3            2019-08-01 17:00:00.000
2          4            2019-08-02 09:00:00.000
2          5            2019-08-02 10:00:00.000
2          4            2019-08-02 11:00:00.000

Expected Output
Callerid   Recipientid     Datecalled
2             5            2019-07-05
2             3            2019-08-01
2             4            2019-08-02

I wrote the below query but can't get it to return recipientid. Any help on this will be appreciated!

select pl.callerid,cast(pl.datecalled as date) as datecalled
from phonelog pl inner join (select callerid, cast(datecalled as date) as datecalled, 
                             min(datecalled) as firstcall, max(datecalled) as lastcall
                             from phonelog
                             group by callerid, cast(datecalled as date)) as x
on pl.callerid = x.callerid and cast(pl.datecalled as date) = x.datecalled
and (pl.datecalled = x.firstcall or pl.datecalled = x.lastcall)
group by pl.callerid, cast(pl.datecalled as date) 
having count(distinct recipientid) = 1

Upvotes: 1

Answers (7)

Bob Boroujerdi Far

Reputation: 39

Its very easy with window function

WITH cte AS (
SELECT *, CAST(DateCalled as DATE) DateCalled
,FIRST_VALUE(Recipientid) OVER (PARTITION BY Callerid  ,CAST(DateCalled as date) ORDER BY CAST(DateCalled AS DATE)) f
,LAST_VALUE(Recipientid) OVER (PARTITION BY Callerid  ,CAST(DateCalled as date) ORDER BY CAST(DateCalled AS DATE)) l
FROM phonelog 
)

SELECT DISTINCT Callerid,Recipientid, DateCalled FROM cte 
WHERE  f=l

Upvotes: 2

Swati

Reputation: 1

I think we need to identify first and last call made by caller on a day and then compare it with first and last call by caller to a recipient for that day. Below code has firstcall and lastcall made by caller on a day. Then it finds first and last call by caller to respective recipient and then compare.

    SELECT DISTINCT 
            callerid,
    recipientid,
    CONVERT(date,firstcall) 
    FROM
    (
      Select 
        callerid,
        recipientid,
        MIN(dateCalled) OVER(PARTITION BY callerid,CONVERT(date,DateCalled)) as firstcall,
        MAX(DateCalled) OVER(PARTITION BY callerid,CONVERT(date,DateCalled)) as lastcall,
        MIN(DateCalled) OVER(PARTITION BY callerid,recipientid,convert(date,DateCalled)) as recipfirstcall,
        MAX(call_start_time) OVER(PARTITION BY callerid,recipientid,convert(date,DateCalled)) as reciplastcall
    from phonelog
) as A 
where A.firstcall=A.recipfirstcall and A.lastcall=A.reciplastcall

Upvotes: 0

Raushan Kumar

Reputation: 1

As per my understanding we have to rank Caller_id as well as Recipient_id along with the Date. Below is my solution which is working well for this case.

with CTE as 
(select *,
row_number() over (partition by callerid, convert(VARCHAR,datecalled,23) order by convert(VARCHAR,datecalled,23)) as first_recipient_id,
row_number() over (partition by receipientid, convert(VARCHAR,datecalled,23) order by convert(VARCHAR,datecalled,23) desc) as last_recipient_id
from activity
)
       
select t.callerid,t.receipientid,CONVERT(VARCHAR,t.datecalled) as DateCalled from CTE t      
where t.first_recipient_id >1 AND t.last_recipient_id>1;

The result that I was able to get:

Result

Upvotes: 0

DRapp

Reputation: 48169

Another dbFiddle option

First, my prequery (PQ alias), I am getting for a given client, per day, the min and max time called but also HAVING to make sure person had at least 2 phone calls in a given day. From that, I re-join to the phone log table on the FIRST (MIN) call for the person for the given day. Then I join one more time for the LAST (MAX) call for the same person for the same day and make sure the recipient of the first is same as last.

I do not have to join on the stripped-down "JustDate" column used for the grouping as the MIN/MAX qualifies the FULL date/time.

select
       PQ.JustDate,
       PQ.CallerID,
       pl1.RecipientID
    from
        ( select
                callerID,
                convert( date, dateCalled ) JustDate,
                min( DateCalled ) minDateCall,
                max( DateCalled ) maxDateCall
            from
                PhoneLog pl
            group by
                callerID,
                convert( date, dateCalled ) 
            having
                count(*) > 1) PQ
            JOIN PhoneLog pl1
                on PQ.CallerID = pl1.CallerID
                AND PQ.minDateCall = pl1.dateCalled
                JOIN PhoneLog pl2
                    on PQ.CallerID = pl2.CallerID
                    AND PQ.maxDateCall = pl2.dateCalled
                    AND pl1.RecipientID = pl2.RecipientID

Upvotes: 2

Nick

Reputation: 147216

You can use a CTE to compute the first and last call of each day by Callerid, and then self-JOIN that CTE to find callers whose first and last calls were to the same Recipientid:

WITH CTE AS (
  SELECT Callerid, RecipientId, CONVERT(DATE, Datecalled) AS Datecalled,
         ROW_NUMBER() OVER (PARTITION BY Callerid, CONVERT(DATE, Datecalled) ORDER BY Datecalled) AS rna,
         ROW_NUMBER() OVER (PARTITION BY Callerid, CONVERT(DATE, Datecalled) ORDER BY Datecalled DESC) AS rnb  
  FROM phonelog
)
SELECT c1.Callerid, c1.RecipientId, c1.Datecalled
FROM CTE c1
JOIN CTE c2 ON c1.Callerid = c2.Callerid AND c1.Recipientid = c2.Recipientid
WHERE c1.rna = 1 AND c2.rnb = 1

Output:

Callerid    RecipientId     Datecalled
2           5               2019-07-05
2           3               2019-08-01
2           4               2019-08-02

Demo on SQLFiddle

Upvotes: 1

sticky bit

Reputation: 37482

Since SQL Server 2019 you could use the first_value() and last_value() window functions.

SELECT DISTINCT
       x1.callerid,
       x1.fri,
       x1.datecalled
       FROM (SELECT pl1.callerid,
                    pl1.recipientid,
                    convert(date, pl1.datecalled) datecalled,
                    first_value(pl1.recipientid) OVER (PARTITION BY pl1.callerid,
                                                                    convert(date, pl1.datecalled)
                                                       ORDER BY pl1.datecalled
                                                       RANGE BETWEEN UNBOUNDED PRECEDING
                                                                     AND UNBOUNDED FOLLOWING) fri,
                    last_value(pl1.recipientid) OVER (PARTITION BY pl1.callerid,
                                                                  convert(date, pl1.datecalled)
                                                      ORDER BY pl1.datecalled
                                                      RANGE BETWEEN UNBOUNDED PRECEDING
                                                                     AND UNBOUNDED FOLLOWING) lri
                    FROM phonelog pl1) x1
       WHERE x1.fri = x1.lri;

In older versions you can use correlated subqueries with TOP 1.

SELECT DISTINCT
       x1.callerid,
       x1.fri,
       x1.datecalled
       FROM (SELECT pl1.callerid,
                    pl1.recipientid,
                    convert(date, pl1.datecalled) datecalled,
                    (SELECT TOP 1
                            pl2.recipientid
                            FROM phonelog pl2
                            WHERE pl2.callerid = pl1.callerid
                                  AND pl2.datecalled >= convert(date, pl1.datecalled)
                                  AND pl2.datecalled < dateadd(day, 1, convert(date, pl1.datecalled))
                            ORDER BY pl2.datecalled ASC) fri,
                    (SELECT TOP 1
                            pl2.recipientid
                            FROM phonelog pl2
                            WHERE pl2.callerid = pl1.callerid
                                  AND pl2.datecalled >= convert(date, pl1.datecalled)
                                  AND pl2.datecalled < dateadd(day, 1, convert(date, pl1.datecalled))
                            ORDER BY pl2.datecalled DESC) lri
                    FROM phonelog pl1) x1
       WHERE x1.fri = x1.lri;

db<>fiddle

If you don't want to return log rows where somebody just made one call on a day, which of course means the first and the last call of the day were to the same person, you can use GROUP BY and HAVING count(*) > 1 instead of DISTINCT.

SELECT x1.callerid,
       x1.fri,
       x1.datecalled
       FROM (...) x1
       WHERE x1.fri = x1.lri
       GROUP BY x1.callerid,
                x1.fri,
                x1.datecalled
       HAVING count(*) > 1;

Upvotes: 1

Nguyễn Văn Phong

Reputation: 14228

As my understanding, you want to select callerid with each Recipientid with the times greater than 1 to make sure that we have First call and Last call. So you just need to group by 3 columns combine with having count(Recipientid) > 1 Like this

 SELECT Callerid, Recipientid, CAST(Datecalled AS DATE) AS Datecalled
 FROM phonelog
 GROUP BY Callerid, Recipientid, CAST(Datecalled AS DATE)
 HAVING COUNT(Recipientid) > 1

Demo on db<>fiddle

Upvotes: -1

Caller whose first and last call was to the same person

Answers (7)

Related Questions