I have a query that is intended to select a single alternate phone number from a record (There can be as many as 10 additional numbers for each client). It works fine if there is 1 default phone number and 1 alternate phone (Defined by default = 1 and 0). If there is more than 1 alternate phone number , it is instead duplicating the entire row and adding the distinct alternate phone numbers. If a record has 4 alternate numbers and 1 default, I will have 4 rows with every value being duplicated except the default phone number. I don't care which of the alternate numbers I select but I want one row with the default number and one of the alternate numbers.I'd appreciate it if someone could show me what I am doing wrong here. Thanks for your help
Select
p.[id][Personal ID],
ISNULL(p.[first_name] + ' ' ,'') +
ISNULL(p.[middle_name] + ' ' ,'') +
ISNULL(p.[last_name] + ' ' ,'') [Name],
p.SocSec [SSEC],
ISNULL(a.unit_designator + ' ','') + ISNULL(a.unit_designator_value + ' ','') + ISNULL(a.street_number + ' ','') + ISNULL(a.street_number_suffix + ' ','') + ISNULL(a.street_name + ' ','') + ISNULL(a.street_type + ' ','')[Person Address Line 1],
ISNULL(a.city, '') [City],
ISNULL(a.state, '') [State],
ISNULL(a.zip, '') [Zip Code],
e.[address][Email],
Convert(date,p.birthdate) [Person Birthday],
ISNULL(ph.area_code,'') +
ISNULL(ph.phone_prefix,'') +
ISNULL(ph.phone_suffix,'') [Default Phone],
--Alternative phone
(Select top 1 ISNULL(pho.area_code,'') + ISNULL(pho.phone_prefix,'') + ISNULL(pho.phone_suffix,'')) [Person Alt Phone Number]
FROM [people] p
--Address
LEFT OUTER JOIN [Addref] pax ON p.[id] = pax.id
INNER JOIN [address] a ON pax.addressid = a.addressid
left outer join emailref pex on p.id = pex.id
Inner Join email e on e.emailid = pex.emailid
--Phone (Main)
LEFT OUTER JOIN phoneref ppx ON p.[id] = ppx.id
INNER JOIN [phone] ph ON ppx.phoneid = ph.phoneid
--Phone Alternate
LEFT OUTER JOIN phoneref pppx ON p.[id] = pppx.id
INNER JOIN [phone] pho ON pppx.phone_id = pho.phoneid
where (pax.[default]= 1)and (pex.[default] = 1) and (ppx.[default] = 1) and (pppx.[default] = 0)
August 22, 2020 at 8:53 am
Can you please post the DDL (create table) script, sample data as an insert statement and the desired output from the sample data!
😎
You need to pivot the 'phone numbers.
Also it looks as though some of your outer joins should be nested.
Search for both of these.
If you want tested code you need to post DDL. (ie a test harness.)
One approach:
WITH PhoneNumbers
AS
(
SELECT D.id
,MAX
(
CASE
WHEN rn = 1
THEN ISNULL(D.area_code,'') + ISNULL(D.phone_prefix,'') + ISNULL(D.phone_suffix,'')
END
) AS DefaultPhone
,MAX
(
CASE
WHEN rn = 2
THEN ISNULL(D.area_code,'') + ISNULL(D.phone_prefix,'') + ISNULL(D.phone_suffix,'')
END
) AS AltPhone
FROM
(
SELECT ppx.id, ph.area_code, ph.phone_prefix, ph.phone_suffix
,ROW_NUMBER() OVER (PARTITION BY ppx.id ORDER BY ppx.[default] DESC, ppx.phoneid) AS rn
FROM phoneref ppx
JOIN phone ph
ON ppx.phoneid = ph.phoneid
) D
WHERE D.rn < 3
GROUP BY D.id
)
SELECT <yourcols>, N.DefaultPhone, N.AltPhone
FROM [people] p
--Address
-- Either have to make both outer joins or nest the join
-- otherwise all will be converted to inner joins
LEFT OUTER JOIN
(
[Addref] pax
INNER JOIN [address] a ON pax.addressid = a.addressid
) ON p.[id] = pax.id
-- Either have to make both outer joins or nest the join
-- otherwise all will be converted to inner joins
LEFT OUTER JOIN
(
emailref pex
Inner Join email e on e.emailid = pex.emailid
) ON p.id = pex.id
--Phone (Main)
JOIN PhoneNumbers N
ON P.id = N.id
WHERE pax.[default]= 1
AND pex.[default] = 1;
August 25, 2020 at 3:17 pm
Thanks for the direction. I was able to adapt this to work for me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply