CODEWITHSUNDEEP
sqlsql-serverstored-procedures
MF Luder
MF Luder

Reputation: 379

SQL Server stored procedure - pull columns from multiple rows into one

I'm working on a SQL stored procedure to pull patient billing data to a dataset (which will then be returned to a VB.net application).

My problem: one patient may have multiple rows in the table for one billing period/item, but only 3 of the fields in the rows will be different. I don't want to pull each row in its entirety every time. I want all fields the first time around, then just the different fields the next time around. I would like all records in one row.

I have no way of knowing ahead of time how many rows this patient will have in the table for this one billing period/item, but the maximum is 10.

There is a claim_id field in the table that is patient specific to each billing period/item, so it can be repeated (and is what I'm using to pull the records). There is also an ID field in the table that is an Identity column, so it is unique for every record.

My current stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [BillClm] 
@ClaimId varchar(20),
@AccountCode integer

AS

SET NOCOUNT ON;

declare @PatientGridTable table
(
    Claim_Submitters_Id varchar(20),
    Claim_Status_Code varchar(2),
    Total_Amount_of_Claim decimal(18,2),
    Claim_Payment_Amt decimal(18,2),
    Claim_Patient_Resp decimal(18,2),
    Claim_Filing_Ind_Code varchar(2),
    Payor_Claim_Control_Num varchar(30),
    Bill_Type integer,
    Claim_Adjustment_Group_Reason_Code varchar(10),
    Claim_Adjustment_Amount decimal(18,2),
    Claim_Adjustment_Quantity integer,  
    Patient_Name varchar(60),
    Patient_ID_Num varchar(50),
    Insured_Name varchar(60),
    Insured_ID_Num varchar(50),
    Other_Payor_Name varchar(60),
    Other_Payor_ID varchar(80),
    Other_Subscriber_Name varchar(60),
    Other_Subscriber_ID varchar(80),
    Covered_Quantity integer,
    Covered_Amount decimal(18,2),
    Reimbursement_Rate decimal(6,2),
    Remark_Code varchar(2500),  
    Statement_Period_Start_End varchar(25)
)

insert into @PatientGridTable
(
    Claim_Submitters_Id,
    Claim_Status_Code,
    Total_Amount_of_Claim,
    Claim_Payment_Amt,
    Claim_Patient_Resp,
    Claim_Filing_Ind_Code,
    Payor_Claim_Control_Num,
    Bill_Type,
    Claim_Adjustment_Group_Reason_Code,
    Claim_Adjustment_Amount,
    Claim_Adjustment_Quantity,  
    Patient_Name,
    Patient_ID_Num,
    Insured_Name,
    Insured_ID_Num,
    Other_Payor_Name,
    Other_Payor_ID,
    Other_Subscriber_Name,
    Other_Subscriber_ID,
    Covered_Quantity,
    Covered_Amount,
    Reimbursement_Rate,
    Remark_Code,    
    Statement_Period_Start_End
)
select  
    d.submitter_id, d.claim_status, d.claim_amt, d.payment_amt,
    d.patient_resp, d.claim_ind, d.ref_num, d.bill_type,
    (a.adj_group_code+' '+a.adj_reason_code), a.adj_amount,
    a.adj_qty, d.patient_name, d.patient_id, d.insured_name,
    d.insured_id, d.other_payor, d.other_payor_id, d.other_subscriber,
    d.other_subscriber_id, d.days, d.amount, d.percent,
    (d.adj_ref+'_'+c.code_text),
    (CONVERT(varchar(12), d.claim_start_date, 101)+' '+CONVERT(varchar(12),         d.claim_end_date,101))
    FROM clm_detail_patient d  WITH(NOLOCK)
    INNER JOIN code_list c  WITH(NOLOCK) 
    ON d.adj_ref = c.map_id
    INNER JOIN clm_adj a  WITH(NOLOCK)
    ON d.claim_id = a.claim_id    
    WHERE d.claim_id = @ClaimId     

-- -----------------------------------------------------------------------
select * from @PatientGridTable

clm_adj is the table that may return 0 to 10 rows - tied to the clm_detail_patient table via the claim_id field. (clm_detail_patient will always have only one row.)

The three fields in the clm_adj table that would be different in each row are: Claim_Adjustment_Group_Reason_Code, Claim_Adjustment_Amount, Claim_Adjustment_Quantity

What I'd like is for those three fields to appear next to each other in one row for each different row in clm_adj. I'm not sure how to accomplish that.

I'm not tied to doing a temp table, if there is a better way to go here. Any help would be much appreciated!

Sample clm_detail_patient table data:

id     code      amt        payment    pat_resp   clm_ind    ref_num          bill     pat_name          pat_id      days    amount      pct   adj_ref  start_dt      end_date       claim_id
12345    19    15344.43    14962.12    2456.50    MA        20122580231ABC    213    DOE, JANE           123456789B    0    17418.62    0.00    MA01    2012-10-01    2012-10-31    1234-A00000000000001
12346    19    9157.21     8128.58     289.00     MA        20122580231ABC    212    SMITH, JOHN         987654321B    0    8417.58     0.00    MA01    2012-10-10    2012-10-31    4567-A00000000000001
12347    1     2522.99     1143.66     0.00       MA        20122580231ABC    211    JONES, MARY         987123456B    0    1143.66     0.00    MA01    2012-10-14    2012-10-17    9876-A00000000000001

Sample clm_adj table data:

adj  reason   amt      qty   claim_id
CO    45    -2074.19    0    1234-A00000000000001
PR    2     2456.50     0    1234-A00000000000001
CO    45    739.63      0    4567-A00000000000001
PR    2     289.00      0    4567-A00000000000001
CO    45    1379.33     0    9876-A00000000000001

Last field in each table is the claim_id.

So the first two patients have two records in the clm_adj table, the third patient has just one.

If I were to run my current stored procedure for patient # 1, I'd get two rows back. What I'd like to see for the first patient:

12345    19    15344.43    14962.12    2456.50    MA        20122580231ABC    213    CO 45    -2074.19    0    PR 2    2456.50    0    DOE, JANE           123456789B    0    17418.62    0.00    MA01    2012-10-01  2012-10-31    1234-A00000000000001

So the two adj/reason, amount, and quantity field records appear on one row, next to each other (starting with the CO 45 in the ninth field here).

Upvotes: 2

Views: 3088

Answers (1)

MF Luder
MF Luder

Reputation: 379

Following the links provided by Esoteric Screen Name I was able to get the problem solved. I inserted the following into my stored procedure prior to declaring the patient grid table:

DECLARE @combinedClaimString VARCHAR(MAX)
SELECT @combinedClaimString = COALESCE(@combinedClaimString + '; ', '') + adj_group_code + ', ' + adj_reason_code + ', ' + (SELECT CONVERT(varchar(22), adj_amount)) + ', ' + (SELECT CONVERT(varchar(22), adj_qty))
FROM (SELECT  adj_group_code, adj_reason_code, adj_amount, adj_qty, claim_id 
FROM br549.clm_adj
WHERE claim_id = @ClaimId)
AS temp

This results in one string with each part of the claim separated by commas, with each individual claim separated by a semi-colon.

I then modified the PatientGridTable structure to include just one field for all claim information:

declare @PatientGridTable table
(
    Claim_Submitters_Id varchar(20),
    Claim_Status_Code varchar(2),
    Total_Amount_of_Claim decimal(18,2),
    Claim_Payment_Amt decimal(18,2),
    Claim_Patient_Resp decimal(18,2),
    Claim_Filing_Ind_Code varchar(2),
    Payor_Claim_Control_Num varchar(30),
    Bill_Type integer,
    Claim_Adjustment varchar(MAX),  
    Patient_Name varchar(60),
    Patient_ID_Num varchar(50),
    Insured_Name varchar(60),
    Insured_ID_Num varchar(50),
    Other_Payor_Name varchar(60),
    Other_Payor_ID varchar(80),
    Other_Subscriber_Name varchar(60),
    Other_Subscriber_ID varchar(80),
    Covered_Quantity integer,
    Covered_Amount decimal(18,2),
    Reimbursement_Rate decimal(6,2),
    Remark_Code varchar(2500),  
    Statement_Period_Start_End varchar(25)
)

I then selected the @combinedClaimString variable into the PatientGridTable: 

insert into @PatientGridTable
(
    Claim_Submitters_Id,
    Claim_Status_Code,
    Total_Amount_of_Claim,
    Claim_Payment_Amt,
    Claim_Patient_Resp,
    Claim_Filing_Ind_Code,
    Payor_Claim_Control_Num,
    Bill_Type,
    Claim_Adjustment,  
    Patient_Name,
    Patient_ID_Num,
    Insured_Name,
    Insured_ID_Num,
    Other_Payor_Name,
    Other_Payor_ID,
    Other_Subscriber_Name,
    Other_Subscriber_ID,
    Covered_Quantity,
    Covered_Amount,
    Reimbursement_Rate,
    Remark_Code,    
    Statement_Period_Start_End
)
select  
    d.submitter_id, d.claim_status, d.claim_amt, d.payment_amt,
    d.patient_resp, d.claim_ind, d.ref_num, d.bill_type,
    (SELECT @combinedClaimString as adj_group_code), d.patient_name, d.patient_id, d.insured_name, d.insured_id, d.other_payor, d.other_payor_id, d.other_subscriber,
    d.other_subscriber_id, d.days, d.amount, d.percent,
    (d.adj_ref+'_'+c.code_text),
    (CONVERT(varchar(12), d.claim_start_date, 101)+' '+CONVERT(varchar(12),         d.claim_end_date,101))
    FROM clm_detail_patient d  WITH(NOLOCK)
    INNER JOIN code_list c  WITH(NOLOCK) 
    ON d.adj_ref = c.map_id
    INNER JOIN clm_adj a  WITH(NOLOCK)
    ON d.claim_id = a.claim_id    
    WHERE d.claim_id = @ClaimId

The dataset is returned to my application with just one row per patient. I can then split the claim string to format appropriately.

Many thanks to Esoteric Screen Name!

Upvotes: 1

Related Questions