none
How to pirioritize the phone nos from different columns RRS feed

  • Question

  • Hello Evening

     


    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

    Friday, September 11, 2020 2:30 AM