I have mobile data with the persons Name, the date of the call, the from_number and the to_number. I need to have the association of the person's name that was called.
That is, if I have Barbara calling from number x to number y then I want to know to whom that y or to_number belongs to?
Below is an example data_set:
SELECT 'Barbara' AS Person, '2020-07-03' AS Date,'022445544' AS From_Number, '022445545' AS To_Number UNION
SELECT 'Barbara' AS Person, '2020-07-03' AS Date,'091234123' AS From_Number, '022445545' AS To_Number UNION
SELECT 'Peter' AS Person, '2020-07-03' AS Date, '03123456' AS From_Number, '08007543' AS To_Number UNION
SELECT 'Peter' AS Person, '2020-07-03' AS Date, '03123456' AS From_Number, '022445555' AS To_Number UNION
SELECT 'Peter' AS Person, '2020-07-04' AS Date, '022445545' AS From_Number, '091234123' AS To_Number UNION
SELECT 'Peter' AS Person, '2020-07-04' AS Date, '022445545' AS From_Number, '022445555' AS To_Number UNION
SELECT 'Peter' AS Person, '2020-07-04' AS Date, '022445545' AS From_Number, '049876543' AS To_Number UNION
SELECT 'David' AS Person, '2020-07-03' AS Date, '049876543' AS From_Number, '022445544' AS To_Number UNION
SELECT 'David' AS Person, '2020-07-04' AS Date, '022445555' AS From_Number, '022445545' AS To_Number
I tried this:
select *
,case when [From_Number] =[To_Number] then [Person] end as [from person]
from [dbo].[cellphone]
but I think the problem is that I don't know, how would you express that SQL needs to look for all possible numbers among one person of each person, because Barbara for example has many different phone numbers. So as if I would look for a range of numbers to pinpoint the caller number? Sometimes, of course we do not have any association as the number is unknown to us. This would then need to be noted as unknown number.
The desired dataset outcome should look like this:
SELECT 'Barbara' AS Person, '2020-07-03' AS Date,'022445544' AS From_Number, '022445545' AS To_Number,'Peter' AS person_called UNION
SELECT 'Barbara' AS Person, '2020-07-03' AS Date,'091234123' AS From_Number, '022445545' AS To_Number, 'Peter' AS person_called UNION
SELECT 'Peter' AS Person, '2020-07-03' AS Date, '03123456' AS From_Number, '08007543' AS To_Number, 'unkn' AS person_called UNION
SELECT 'Peter' AS Person, '2020-07-03' AS Date, '03123456' AS From_Number, '022445555' AS To_Number, 'David' AS person_called UNION
SELECT 'Peter' AS Person, '2020-07-04' AS Date, '022445545' AS From_Number, '091234123' AS To_Number, 'Barbara'AS person_called UNION
SELECT 'Peter' AS Person, '2020-07-04' AS Date, '022445545' AS From_Number, '022445555' AS To_Number, 'David' AS person_called UNION
SELECT 'Peter' AS Person, '2020-07-04' AS Date, '022445545' AS From_Number, '049876543' AS To_Number, 'David' AS person_called UNION
SELECT 'David' AS Person, '2020-07-03' AS Date, '049876543' AS From_Number, '022445544' AS To_Number, 'Barbara'AS person_called UNION
SELECT 'David' AS Person, '2020-07-04' AS Date, '022445555' AS From_Number, '022445545' AS To_Number , 'Peter' AS person_called
I also tried this :
SELECT A.Person, A.Date, A.From_Number, A.To_Number
,CASE WHEN EXISTS (SELECT B.Person, B.Date, B.From_Number, B.To_Number FROM [dbo].[mobile_data] B
WHERE B.From_Number = A.To_Number)
THEN B.Person = A.Person ELSE 'Unknown' END as caller_id
from [dbo].[mobile_data] a
But I get the following Error MSG: The multi-part identifier "B.Person" could not be bound. Essentially I need : THEN B.Person ELSE
But even if I try this It says that B.Person cannot be bound.
Where is the syntax mistake?
