CODEWITHSUNDEEP
sqlsql-serversql-server-2008
M005
M005

Reputation: 854

Convert specific column into row as output in sql server

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

Upvotes: 1

Views: 67

Answers (1)

caiohamamura
caiohamamura

Reputation: 2728

As I mentioned before, you have a pretty serious database design issue that make the queries difficult. Anyway there it is:

SELECT Patient_First_Name,
Patient_Last_Name,
CASE RIGHT(CategoryId,5)
    WHEN 'COINS' THEN Coins
    WHEN 'COPAY' THEN Copay
    ELSE Deductible END StrangeValue,
CASE WHEN categoryId IS NULL THEN 'MED_DED'
    ELSE categoryId END categoryId
FROM claim c
    LEFT JOIN Eligibility e
        ON e.Dependent_First_Name IS NULL
            AND e.Dependent_Last_Name IS NULL
            AND categoryid IS NOT NULL
            AND (Service_Type = 'H' AND CategoryId IN ('MED_COPAY', 'MED_DED', 'MED_COINS'))
ORDER BY patient_first_name DESC, patient_last_name, categoryid, coins DESC

I'm not really sure I understood your needs, but that will return what you said you expected.

EDIT: Sorry

SELECT Patient_First_Name,
Patient_Last_Name,
CASE RIGHT(CategoryId,5)
    WHEN 'COINS' THEN Coins
    WHEN 'COPAY' THEN Copay
    ELSE Deductible END claimValue,
CASE WHEN categoryId IS NULL THEN 'MED_DED'
    ELSE categoryId END categoryId
FROM claim c
    LEFT JOIN Eligibility e
        ON e.Dependent_First_Name IS NULL
            AND CategoryId IN ('MED_COPAY', 'MED_DED', 'MED_COINS')
WHERE (CategoryId = 'MED_COINS' AND Coins IS NOT NULL) OR
    (CategoryId = 'MED_COPAY' AND Copay IS NOT NULL) OR
    (CategoryId = 'MED_DED' AND Deductible IS NOT NULL)
ORDER BY patient_first_name DESC, patient_last_name, categoryid, claimValue DESC

Upvotes: 1

Related Questions