locked
Convert column to raw based on condition in SQL Server RRS feed

  • Question

  • User-2125602596 posted

    There are two tables

    1. Eligibility
    2. Claim

    In eligibility table if employee has categoryId like MED_COPAY, MED_COINS, RX_DED, RX_COPAY then Dependent should also eligible for these categories.

    So basically I need following type of output where coins, copay & deductible columns should be in separate rows, if employee does not have categoryID = MED_DED so it should not include that record into output for employee & dependent record.

    Service Type column in claim table as follow

    H=Medical (MED_COPAY,MED_DED & MED_COINS)

    P= Pharma (RX_COPAY,RX_DED & RX_COINS)

    here is claim table

    +--------------------+-------------------+-------+-------+------------+-----------+--------------+
    | Patient First Name | Patient Last Name | CoIns | Copay | Deductible |    PID    | Service Type |
    +--------------------+-------------------+-------+-------+------------+-----------+--------------+
    | JOHN               | SMITH             | 0.9   | 0.79  | 0          | 144423057 | H            |
    | JOHN               | SMITH             | 0.91  | 0.81  | 0          | 144423057 | H            |
    | JOHN               | SMITH             | 0.92  | 0.82  | 0          | 144423057 | H            |
    | JOHN               | SMITH             | 0.93  | 0.83  | 0          | 144423057 | H            |
    | JOHN               | SMITH             | 0.94  | 0.84  | 0          | 144423057 | H            |
    | JOHN               | SMITH             | 0.95  | 0.85  | 0          | 144423057 | H            |
    | JOHN               | SMITH             | 0.96  | 0.86  | 0          | 144423057 | H            |
    | JOHN               | SMITH             | 0.97  | 0.87  | 0          | 144423057 | H            |
    | JOHN               | SMITH             | NULL  | 0.61  | 19.04      | 144423057 | P            |
    | JOHN               | SMITH             | NULL  | 0.62  | 1.7        | 144423057 | P            |
    | JOHN               | SMITH             | NULL  | 0.63  | 48.14      | 144423057 | P            |
    | CATHERINE L        | WILLIAMS          | 0.1   | 0.71  | 0          | 144423057 | H            |
    | CATHERINE L        | WILLIAMS          | 0.2   | 0.72  | 0          | 144423057 | H            |
    | CATHERINE L        | WILLIAMS          | 0.3   | 0.73  | 0          | 144423057 | H            |
    | CATHERINE L        | WILLIAMS          | 0.4   | 0.74  | 0          | 144423057 | H            |
    | CATHERINE L        | WILLIAMS          | 0.5   | 0.75  | 0          | 144423057 | H            |
    | CATHERINE L        | WILLIAMS          | NULL  | 0.64  | 19.04      | 144423057 | P            |
    | CATHERINE L        | WILLIAMS          | NULL  | 0.65  | 1.7        | 144423057 | P            |
    | CATHERINE L        | WILLIAMS          | NULL  | 0.66  | 48.14      | 144423057 | P            |
    | CATHERINE L        | WILLIAMS          | 0.6   | 0.76  | 0          | 144423057 | H            |
    | CATHERINE L        | WILLIAMS          | 0.7   | 0.77  | 0          | 144423057 | H            |
    | CATHERINE L        | WILLIAMS          | 0.8   | 0.78  | 0          | 144423057 | H            |
    +--------------------+-------------------+-------+-------+------------+-----------+--------------+

    here is eligibility table

    +------------+----------------------+---------------------+---------------------+--------------------+-----------+
    | categoryId | Dependent First Name | Dependent Last Name | Employee First Name | Employee Last Name |    SID    |
    +------------+----------------------+---------------------+---------------------+--------------------+-----------+
    | MED_COPAY  |                      |                     | CATHERINE L               | WILLIAMS             | 144423057 |
    | MED_COINS  |                      |                     | CATHERINE L             | WILLIAMS             | 144423057 |
    | RX_DED     |                      |                     | CATHERINE L      | WILLIAMS             | 144423057 |
    | RX_COPAY   |                      |                     | CATHERINE L             | WILLIAMS           | 144423057 |
    | RX_DED     | JOHN                 | SMITH               | CATHERINE L             | WILLIAMS           | 144423057 |
    | MED_COPAY  | JOHN                 | SMITH               | CATHERINE L             | WILLIAMS           | 144423057 |
    +------------+----------------------+---------------------+---------------------+--------------------+-----------+

    I am looking following output from above two tables

    +------+-------+-------+-----------+-----------+
    | JOHN | SMITH | 0.9   | 144423057 | MED_COINS |
    | JOHN | SMITH | 0.91  | 144423057 | MED_COINS |
    | JOHN | SMITH | 0.92  | 144423057 | MED_COINS |
    | JOHN | SMITH | 0.93  | 144423057 | MED_COINS |
    | JOHN | SMITH | 0.94  | 144423057 | MED_COINS |
    | JOHN | SMITH | 0.95  | 144423057 | MED_COINS |
    | JOHN | SMITH | 0.96  | 144423057 | MED_COINS |
    | JOHN | SMITH | 0.97  | 144423057 | MED_COINS |
    | JOHN | SMITH | 0.61  | 144423057 | MED_COPAY |
    | JOHN | SMITH | 0.62  | 144423057 | MED_COPAY |
    | JOHN | SMITH | 0.63  | 144423057 | MED_COPAY |
    | JOHN | SMITH | 0.79  | 144423057 | MED_COPAY |
    | JOHN | SMITH | 0.81  | 144423057 | MED_COPAY |
    | JOHN | SMITH | 0.82  | 144423057 | MED_COPAY |
    | JOHN | SMITH | 0.83  | 144423057 | MED_COPAY |
    | JOHN | SMITH | 0.84  | 144423057 | MED_COPAY |
    | JOHN | SMITH | 0.85  | 144423057 | MED_COPAY |
    | JOHN | SMITH | 0.86  | 144423057 | MED_COPAY |
    | JOHN | SMITH | 0.87  | 144423057 | MED_COPAY |
    | JOHN | SMITH | 19.04 | 144423057 | MED_DED   |
    | JOHN | SMITH | 1.7   | 144423057 | MED_DED   |
    | JOHN | SMITH | 48.14 | 144423057 | MED_DED   |
    | CATHERINE L | WILLIAMS | 0.1   | 144423057 | MED_COINS |
    | CATHERINE L | WILLIAMS | 0.2   | 144423057 | MED_COINS |
    | CATHERINE L | WILLIAMS | 0.3   | 144423057 | MED_COINS |
    | CATHERINE L | WILLIAMS | 0.4   | 144423057 | MED_COINS |
    | CATHERINE L | WILLIAMS | 0.5   | 144423057 | MED_COINS |
    | CATHERINE L | WILLIAMS | 0.6   | 144423057 | MED_COINS |
    | CATHERINE L | WILLIAMS | 0.7   | 144423057 | MED_COINS |
    | CATHERINE L | WILLIAMS | 0.8   | 144423057 | MED_COINS |
    | CATHERINE L | WILLIAMS | 0.64  | 144423057 | MED_COPAY |
    | CATHERINE L | WILLIAMS | 0.65  | 144423057 | MED_COPAY |
    | CATHERINE L | WILLIAMS | 0.66  | 144423057 | MED_COPAY |
    | CATHERINE L | WILLIAMS | 0.71  | 144423057 | MED_COPAY |
    | CATHERINE L | WILLIAMS | 0.72  | 144423057 | MED_COPAY |
    | CATHERINE L | WILLIAMS | 0.73  | 144423057 | MED_COPAY |
    | CATHERINE L | WILLIAMS | 0.74  | 144423057 | MED_COPAY |
    | CATHERINE L | WILLIAMS | 0.75  | 144423057 | MED_COPAY |
    | CATHERINE L | WILLIAMS | 0.76  | 144423057 | MED_COPAY |
    | CATHERINE L | WILLIAMS | 0.77  | 144423057 | MED_COPAY |
    | CATHERINE L | WILLIAMS | 0.78  | 144423057 | MED_COPAY |
    | CATHERINE L | WILLIAMS | 19.04 | 144423057 | MED_DED   |
    | CATHERINE L | WILLIAMS | 1.7   | 144423057 | MED_DED   |
    | CATHERINE L | WILLIAMS | 48.14 | 144423057 | MED_DED   |
    +------+-------+-------+-----------+-----------+

    below is SQL script for Claim & Eligibility table

    CREATE TABLE claim(
    Patient_First_Name VARCHAR(20) NULL
    ,Patient_Last_Name  VARCHAR(20) NULL
    ,CoIns              VARCHAR(4)  NULL
    ,Copay              NUMERIC(4,2) NULL
    ,Deductible         NUMERIC(5,2) NULL
    ,PID                INTEGER   NULL
    ,Service_Type       VARCHAR(1)  NULL
      );
    
    INSERT INTO  claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('JOHN', 'SMITH','0.9',0.79,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('JOHN', 'SMITH','0.91',0.81,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('JOHN', 'SMITH','0.92',0.82,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('JOHN', 'SMITH','0.93',0.83,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('JOHN', 'SMITH','0.94',0.84,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('JOHN', 'SMITH','0.95',0.85,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('JOHN', 'SMITH','0.96',0.86,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('JOHN', 'SMITH','0.97',0.87,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('JOHN', 'SMITH',NULL,0.61,19.04,144423057,'P');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('JOHN', 'SMITH',NULL,0.62,1.7,144423057,'P');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('JOHN', 'SMITH',NULL,0.63,48.14,144423057,'P');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('CATHERINE','WILLIAMS','0.1',0.71,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('CATHERINE L','WILLIAMS','0.2',0.72,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('CATHERINE L','WILLIAMS','0.3',0.73,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('CATHERINE L','WILLIAMS','0.4',0.74,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('CATHERINE L','WILLIAMS','0.5',0.75,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('CATHERINE L','WILLIAMS',NULL,0.64,19.04,144423057,'P');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('CATHERINE L','WILLIAMS',NULL,0.65,1.7,144423057,'P');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('CATHERINE L','WILLIAMS',NULL,0.66,48.14,144423057,'P');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('CATHERINE L','WILLIAMS','0.6',0.76,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('CATHERINE L','WILLIAMS','0.7',0.77,0,144423057,'H');
    INSERT INTO claim(Patient_First_Name,Patient_Last_Name,CoIns,Copay,Deductible,PID,Service_Type) VALUES ('CATHERINE L','WILLIAMS','0.8',0.78,0,144423057,'H');
    
    CREATE TABLE Eligibility(
    categoryId           VARCHAR(9)
    ,Dependent_First_Name VARCHAR(20)
    ,Dependent_Last_Name  VARCHAR(20)
    ,Employee_First_Name  VARCHAR(20)
    ,Employee_Last_Name   VARCHAR(20)
    ,SID                  INTEGER 
     );
    
    INSERT INTO Eligibility(categoryId,Dependent_First_Name,Dependent_Last_Name,Employee_First_Name,Employee_Last_Name,SID) VALUES ('MED_COPAY',NULL,NULL,'CATHERINE L ','WILLIAMS',144423057);
    INSERT INTO Eligibility(categoryId,Dependent_First_Name,Dependent_Last_Name,Employee_First_Name,Employee_Last_Name,SID) VALUES ('MED_COINS',NULL,NULL,'CATHERINE L ','WILLIAMS',144423057);
    INSERT INTO Eligibility(categoryId,Dependent_First_Name,Dependent_Last_Name,Employee_First_Name,Employee_Last_Name,SID) VALUES ('RX_DED',NULL,NULL,'CATHERINE L ','WILLIAMS',144423057);
    INSERT INTO Eligibility(categoryId,Dependent_First_Name,Dependent_Last_Name,Employee_First_Name,Employee_Last_Name,SID) VALUES ('RX_COPAY',NULL,NULL,'CATHERINE L ','WILLIAMS',144423057);
    INSERT INTO Eligibility(categoryId,Dependent_First_Name,Dependent_Last_Name,Employee_First_Name,Employee_Last_Name,SID) VALUES ('RX_DED','JOHN','SMITH','CATHERINE L ','WILLIAMS',144423057);
    INSERT INTO Eligibility(categoryId,Dependent_First_Name,Dependent_Last_Name,Employee_First_Name,Employee_Last_Name,SID) VALUES ('MED_COPAY','JOHN','SMITH','CATHERINE L ','WILLIAMS',144423057);

    This is my current code but it seems not working.

    SELECT 
    c.Patient_first_Name,
    c.Patient_last_Name,
       CASE
           WHEN e.categoryid IN('MED_DED',
                                'RX_DED') THEN deductible
           WHEN e.categoryid IN('MED_COINS',
                                'RX_COINS') THEN isnull(coins,0)
           WHEN e.categoryid IN('MED_COPAY',
                                'RX_COPAY') THEN copay
           ELSE 0
       END AS ClaimAmount,
       e.categoryid AS CatergoryId 
    FROM Claim c
    INNER JOIN Eligibility e ON 
    e.SID = c.PID
    
    AND (
      (c.Patient_First_Name = c.Patient_First_Name
           AND c.Patient_Last_Name = c.Patient_Last_Name))
    AND ((c.Service_type = 'P'
      AND e.CategoryID IN('RX_COINS',
                          'RX_COPAY',
                          'RX_DED'))
     OR (c.Service_type = 'H'
         AND e.CategoryID IN('MED_COINS',
                             'MED_COPAY',
                             'MED_DED')))
     order by 1 asc
    Thursday, March 10, 2016 4:34 PM

All replies

  • User-219423983 posted

    Hi manojsitapara,

    if employee does not have categoryID = MED_DED so it should not include that record into output for employee & dependent record.

    In tale “eligibility”, the employee “144423057” doesn’t have the “categoryID” “MED_DED”, according to this rule, the result should not have the records which are about employee “144423057”. But, the expected result may not comply with this rule. If I misunderstand it, please let me know.

    Besides, could you provide more things when it returns “MED_COINS” and when it returns “MED_COPAY”? Just according to the above information, it’s not easy to understand the rule.

    Best Regards,

    Weibo Zhang

    Tuesday, March 15, 2016 2:58 AM