September 11, 2020 at 2:42 am
Hello Evening
please see below code for sample data
CREATE TABLE PHONENOS (CustID Varchar(10), MobilePhone bigint, LandPhone bigint, OfficePhone bigint)
INSERT INTO PHONENOS (100030, 2001231234, 2011231234, 2561231234)
INSERT INTO PHONENOS (100040, 9001231234, NULL, 9561231234)
INSERT INTO PHONENOS (100050, 8001231234, 8011231234, NULL)
INSERT INTO PHONENOS (100060, NULL, 2017231234, NULL)
INSERT INTO PHONENOS (100070, 2031231234, NULL, 2531231234)
INSERT INTO PHONENOS (100080, NULL, 2411231234, 2861231234)
INSERT INTO PHONENOS (100090, 5001231234, 5011231234, 5561231234)
INSERT INTO PHONENOS (100010, NULL, NULL, 6561231234)
INSERT INTO PHONENOS (100020, 7001231234, 7011231234, 7561231234)
INSERT INTO PHONENOS (100930, 3001231234, 3011231234, 3561231234)
from the above table the custId is unique, each customer can have upto 3 phone nos max, but we want to consider only one no with below order..
Home/Land phone is first priority, then Mobile is second priority, final Office phone need to be considered )
if home is null then check for mobile and consider, if mobile is null then consider office phone no..
so final expectation as below
CREATE TABLE OUTPUTPHONENOS (CustID Varchar(10), Phone bigint)
INSERT INTO OUTPUTPHONENOS (100030, 2011231234)
INSERT INTO OUTPUTPHONENOS (100040, 9001231234)
INSERT INTO OUTPUTPHONENOS (100050, 8011231234)
INSERT INTO OUTPUTPHONENOS (100060, 2017231234)
INSERT INTO OUTPUTPHONENOS (100070, 2031231234)
INSERT INTO OUTPUTPHONENOS (100080, 2411231234)
INSERT INTO OUTPUTPHONENOS (100090, 5011231234)
INSERT INTO OUTPUTPHONENOS (100010, 6561231234)
INSERT INTO OUTPUTPHONENOS (100020, 7011231234)
INSERT INTO OUTPUTPHONENOS (100930, 3011231234)
Thanks in advance
Asita
September 11, 2020 at 3:37 am
SELECT CustID, COALESCE(LandPhone, MobilePhone, OfficePhone) AS Phone
FROM PHONENOS
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 11, 2020 at 8:22 pm
Thank you much for your update.
I apologize there is small change that I didnt mentioned in ealrier post I need to consider only ValidMobile =1 for MobilePhone to be used also ValidLand =1 for Landphone to be used same way ValidOffice as well
CREATE TABLE PHONENOS (CustID Varchar(10), MobilePhone bigint, LandPhone bigint, OfficePhone bigint, ValidMobile bit, ValidLand bit, ValidOffice bit, )
INSERT INTO PHONENOS (100030, 2001231234, 2011231234, 2561231234,1,1,1)
INSERT INTO PHONENOS (100040, 9001231234, NULL, 9561231234,1,0,1)
INSERT INTO PHONENOS (100050, 8001231234, 8011231234, NULL,1,1,0)
INSERT INTO PHONENOS (100060, NULL, 2017231234, NULL, 0,1,0)
INSERT INTO PHONENOS (100070, 2031231234, NULL, 2531231234,1,0,1)
INSERT INTO PHONENOS (100080, NULL, 2411231234, 2861231234,0,1,1)
INSERT INTO PHONENOS (100090, 5001231234, 5011231234, 5561231234,1,1,1)
INSERT INTO PHONENOS (100010, NULL, NULL, 6561231234,0,0,1)
INSERT INTO PHONENOS (100020, 7001231234, 7011231234, 7561231234,0,0,0)
INSERT INTO PHONENOS (100930, 3001231234, 3011231234, 35612312341,0,0)
Right now I am using Query as follows but need some more tweaks I am geting three phones if a user has three. I just only in order Land, Mobile, Office by considering the validXXX tp 1
SELECT CUSTID, LandPhone , 'LAND' as PhoneType
from PHONENOS WHERE LandPhone IS NOT NULL AND ValidLand = 1
UNION
SELECT CUSTID, MobilePhone , 'Mobile' as PhoneType
from PHONENOS WHERE MobilePhone IS NOT NULL AND ValidMobile = 1
UNION
SELECT CUSTID, OfficePhone , 'Office' as PhoneType
from PHONENOS WHERE OfficePhone IS NOT NULL AND ValidOffice = 1
please help me
Thanks in advance
Asita
September 12, 2020 at 2:37 am
SELECT CustID, COALESCE(CASE WHEN ValidLand = 0 THEN NULL ELSE LandPhone END,
CASE WHEN ValidMobile = 0 THEN NULL ELSE MobilePhone END,
CASE WHEN ValidOffice = 0 THEN NULL ELSE OfficePhone END) AS Phone
FROM PHONENOS
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply