none
Perform Insert or Update based on other columns in a table

    Question

  • I have a table called 'Benefit Plans' in the following format.


    GroupOrPolicyNum Benefit Plan 2017_Value 2018_Value
    1.1 5.1 NULL TEST2018
    1.2 5.2 TEST2017 NULL
    1.3 5.3 TEST2017 TEST2018

    The above table may have value in 2017_Value (or) 2018_Value (or) both. I need to create a new column (i.e., EffectiveYearIndicator) and insert data as per the table below.

    GroupOrPolicyNum Benefit Plan 2017_Value 2018_Value EffectiveYearIndicator
    1.1 5.1 NULL TEST2018 2018
    1.2 5.2 TEST2017 NULL 2017
    1.3 5.3 TEST2017 TEST2018 2017
    1.3 5.3 TEST2017 TEST2018 2018

    Need to update the EffectiveYearIndicator with 2017 if there is value in '2017_Value' and '2018_Value' is null, with 2018 if there is value in '2018_Value' and '2017_Value' is null. If both are not null, update 2017 for the original row and duplicate the same row with 2018.

    Please let me know, how can this be achieved effectively.

    Thanks in advance.


    Thursday, September 13, 2018 1:13 AM

All replies

  • Hi Govind,

    Please try this.

    create table BenefitPlans
    (
    GroupOrPolicyNum varchar(64),
    BenefitPlan varchar(64),
    [2017_Value] varchar(128),
    [2018_Value] varchar(128),
    EffectiveYearIndicator int
    )
    
    insert into BenefitPlans(GroupOrPolicyNum,BenefitPlan,[2017_Value],[2018_Value]) values 
    ('1.1','5.1',NULL,'TEST2018'),
    ('1.2','5.2','TEST2017',NULL),
    ('1.3','5.3','TEST2017','TEST2018')
    
    --Update
    Update BenefitPlans set EffectiveYearIndicator=CASE WHEN [2017_Value] IS NOT NULL THEN 2017
                                                        WHEN [2017_Value] IS NULL AND [2018_Value] IS NOT NULL THEN 2018
    											   END
    WHERE [2017_Value] IS NOT NULL OR [2018_Value] IS NOT NULL
    --Insert
    INSERT INTO BenefitPlans
    SELECT 
    GroupOrPolicyNum,BenefitPlan,[2017_Value],[2018_Value],2018
    FROM BenefitPlans 
    WHERE [2017_Value] IS NOT NULL AND [2018_Value] IS NOT NULL	
    
    SELECT * FROM BenefitPlans	
    --Output
    /*
    GroupOrPolicyNum                                                 BenefitPlan                                                      2017_Value                                                                                                                       2018_Value                                                                                                                       EffectiveYearIndicator
    ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------
    1.1                                                              5.1                                                              NULL                                                                                                                             TEST2018                                                                                                                         2018
    1.2                                                              5.2                                                              TEST2017                                                                                                                         NULL                                                                                                                             2017
    1.3                                                              5.3                                                              TEST2017                                                                                                                         TEST2018                                                                                                                         2017
    1.3                                                              5.3                                                              TEST2017                                                                                                                         TEST2018                                                                                                                         2018
    

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 13, 2018 2:26 AM
    Moderator
  • Thanks will, it worked.

    Govind K

    Thursday, September 13, 2018 10:05 PM
  • >>  I have a table called 'Benefit Plans' in the following format. <<

    Where is the DDL for this? Did you read the forum posting rules? Now we have to guess about the constraints, the datatypes, the keys and everything else that you didn't tell us. Do you find this to be a little rude?

    Let's start with a skeleton that you can flesh out. I'm guessing as to the key being your group policy number, but it could just as well be the benefit plan identifier or perhaps both of them together as a compound key.

    ISO standards do not like variable names or data element names, that begin with a digit. It means it many, many standards cannot share them. If you remember from your first week in a data modeling class, a data element should have the same name everywhere it appears in your environment, not just in SQL

    CREATE TABLE Benefit_Plans
    (group_nbr  CHAR(3) NOT NULL PRIMARY KEY 
       CHECK(group_nbr LIKE '[0-9'].[0-9']'),
     benefit_plan_id CHAR(3) NOT NULL
       CHECK( benefit_plan_id LIKE '[0-9'].[0-9']'),
     something_value_2017 DECIMAL(12,2),
     something_value_2018 DECIMAL(12,2));

    >> The above table [sic: it is not a table!] may have value in 2017_Value (or) 2018_Value (or) both. I need to create a new column (i.e., EffectiveYearIndicator) and insert data as per the table below. <<

    I see you don't believe in normalization, but let's ignore that. Your effective year indicator is redundant and should be replaced with a computed column or review. You're basically trying to do punch card data processing in which you have to physically materialize things like this. But in SQL, a virtual column is just as real as a base table column.

    GroupOrPolicyNum Benefit Plan 2017_Value 2018_Value EffectiveYearIndicator
    1.1 5.1 NULL TEST2018 2018
    1.2 5.2 TEST2017 NULL 2017
    1.3 5.3 TEST2017 TEST2018 2017
    1.3 5.3 TEST2017 TEST2018 2018

    >> Need to update the EffectiveYearIndicator with '2017' if there is value in '2017_Value' and '2018_Value' is NULL, with '2018' if there is value in '2018_Value' and '2017_Value' is NULL. <<

    We can use a CASE expression for this part of it. 

    >> If both are not NULL, update '2017' for the original row and duplicate the same row with '2018'. << 

    What does this mean? We don't put duplicate rows in a table. That would destroy all the keys. In fact, the whole idea of database systems, not just RDBMS, is to remove redundancy, not increase it. Here's a start.

    SELECT group_nbr,  benefit_plan_id,
          CASE WHEN something_value_2017 IS NOT NULL 
                    THEN '2017'
                    WHEN something_value_2017 IS NULL 
                         AND something_value_2018 IS NOT NULL 
                    THEN '2018'
           ELSE NULL END AS effective_year
      FROM Benefit_Plans; 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, September 13, 2018 11:11 PM
  • Thanks will, it worked.

    Govind K

    Since you have got your answer, please kindly help close the thread by marking useful reply as answer, so that we don't have to click into the thread again.

    Thanks for your contribution.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 14, 2018 7:52 AM
    Moderator