How to prioritize the phone nos values in three columns.

  • asita

    Hall of Fame

    Points: 3904

    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

  • ScottPletcher

    SSC Guru

    Points: 98559

    SELECT CustID, COALESCE(LandPhone, MobilePhone, OfficePhone) AS Phone

    FROM PHONENOS

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • asita

    Hall of Fame

    Points: 3904

    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

  • ScottPletcher

    SSC Guru

    Points: 98559

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply