Indicate the re-occurrence of a from_number in a to

In a phone network there are callers: from_number and receivers: to_numbers. I need a list of all distinct aussi numbers out of the from_number and to_numbers columns/ variables. The list needs to act as a filter and indicate the direction, as in 'from' or 'to'. If the number re-occurred at any location in both fields, that is at any location in the from_number and at any location of the to_numbers field, then this should be indicated as both.

I have an example code: the desired outcome as well what I tried:

    create table calls 
    (
       call_date date,
       from_number varchar(16),
       to_number varchar(16)
    );
    
    INSERT  calls
  VALUES   ('2020-07-03','619876544',  '022445545'),
         ( '2020-07-03','61123456',  '642445544'), 
         ('2020-07-03','03123456',   '61333333'),
         ('2020-07-03','65123456',   '619876543'),
         ('2020-07-04','642445545',  '61123456'),
         ( '2020-07-04','61333333',  '632445555'),
         ('2020-07-04','642445545',  '049876543'),
         ('2020-07-03','649876543',  '61333333'),
         ( '2020-07-04','612445555',  '022445545');
    
    SELECT   DISTINCT 
                callers.*, 
 
    FROM        calls    callers

the desired outcome with the direction : to, from, both:

create table calls 
(
   call_date date,
   from_number varchar(16),
   to_number varchar(16),
   direction varchar (8)
);

INSERT  calls
VALUES   ('2020-07-03','619876544',  '022445545', 'from'),
         ( '2020-07-03','61123456',  '642445544', 'both'), 
         ('2020-07-03','03123456',   '61333333', 'both'),
         ('2020-07-03','65123456',   '619876543', 'to'),
         ('2020-07-04','642445545',  '61123456', 'both'),
         ( '2020-07-04','61333333',  '632445555', 'both'),
         ('2020-07-04','642445545',  '049876543', 'none'),
         ('2020-07-03','649876543',  '61333333', 'both'),
         ( '2020-07-04','612445555',  '022445545', 'from');

SELECT   DISTINCT 
            callers.*
        
FROM        calls    callers

How can I see, whether a number re-occurred anywhere in [From_Number] AND [To_Number] and set the direction to both in this case? If it only occurred in a from Number, then it should be set to from, if it occurred only in a to number then should be set to : to

what I tried:

create table calls 
(
   call_date date,
   from_number varchar(16),
   to_number varchar(16),
   direction varchar (8)
);

INSERT  calls
VALUES   ('2020-07-03','619876544',  '022445545', 'to'),
         ( '2020-07-03','61123456',  '642445544', 'both'), 
         ('2020-07-03','03123456',   '61333333', 'both'),
         ('2020-07-03','65123456',   '619876543', 'to'),
         ('2020-07-04','642445545',  '61123456', 'both'),
         ( '2020-07-04','61333333',  '632445555', 'both'),
         ('2020-07-04','642445545',  '049876543', '0'),
         ('2020-07-03','649876543',  '61333333', 'both'),
         ( '2020-07-04','612445555',  '022445545', 'from');

SELECT   DISTINCT 
            callers.call_date
            ,callers.[From_Number]
            ,callers.[To_Number]
            ,DIRECTION = ISNULL(recipients.From_Number, 'both')
            --,CASE
            --WHEN LEFT (callers.[From_Number] ,2) = '61' then 'from' 
            --WHEN LEFT (callers.[To_Number] ,2)    = '61' then 'to' ELSE '0' END AS  direction1
FROM        calls    callers
LEFT JOIN   calls    recipients 
ON recipients.from_number = callers.to_number;

Lent on a previous question it is likely to be something like this. How can I set the:

,DIRECTION = ISNULL(recipients.From_Number, 'both')

in a way that it does what I need and DIRECTION can be filtered for to / from / both ?

CodePudding user response：

This is a good place to unpivot using apply:

select v.number,
       (case when min(v.direction) = max(v.direction)
             then min(v.direction)
             else 'both'
        end),
       count(*)
from calls c cross apply
     (values (from_number, 'from'),
             (to_number, 'to')
     ) v(number, direction)
group by number;

CodePudding user response：

Given that you say there is only ever one side which starts with 61, and these are the ones you want to count to establish the direction calls have been made, you can calculate which number to use inside an APPLY, then use window functions over that:

SELECT c.*,
  CASE WHEN COUNT(CASE WHEN from_number LIKE '61%' THEN 1 END) OVER (PARTITION BY v.num) > 0
    THEN
      CASE WHEN COUNT(CASE WHEN to_number LIKE '61%' THEN 1 END) OVER (PARTITION BY v.num) > 0
        THEN 'both'
        ELSE 'from'
      END
    ELSE
      CASE WHEN COUNT(CASE WHEN to_number LIKE '61%' THEN 1 END) OVER (PARTITION BY v.num) > 0
        THEN 'to'
        ELSE 'none'
      END
  END
FROM calls c
CROSS APPLY (VALUES
  (CASE WHEN from_number LIKE '61%' THEN from_number
        WHEN to_number LIKE '61%' THEN to_number
        END)
) v(num);

db<>fiddle