Claims data - counting service lines with the same categories in 2 columns

Analytics

Claims data - counting service lines with the same categories in 2 columns

Hello...relatively new Teradata user here with an issue that seems simple but having trouble executing.

 

I have an answerset of claims, most of which have several service lines, and I'm trying to compare categories of two columns together to determine if they are the same or not.

 

Some simple samples (de-identified):

 

CLAIM_NBR  SERV_LINE MCC1 MCC2

xxxxxx1               1               ER     Other ED Expense

xxxxxx1               2             Specialist   Specialist

xxxxxx2               3               ER             ER

 

So to explain: claim x1 has two service lines, lines 1 & 2, of which line 2 has the same category (Specialist), whereas line 1 does not have the same. Claim x2 has one service line, line 3, which has the same category (ER).

 

Ideally, then I'd be counting 2 separate claims (x1, x2), the first of which had the same category 1/2 (50%) of the time, the second of which had the same category 1/1 (100%) of the time.

 

I think that the following code may work if it is modified; however, the current output I'll attach (without the claims).

 

Thank you in advance for any guidance.

 

Best, Karen

 

SELECT T.CLAIM_NBR, T.SERV_LINE, T.MEDICAL_COST_CATEGORY_1_DESC, T.MEDICAL_COST_CATEGORY_2_DESC

,SUM(CASE WHEN T.MEDICAL_COST_CATEGORY_1_DESC = T.MEDICAL_COST_CATEGORY_2_DESC THEN 1 ELSE 0 END) OVER(PARTITION BY CLAIM_NBR)

 

FROM

(

SELECT S.CLAIM_NBR, S.SERV_LINE, S.MEDICAL_COST_CATEGORY_1_DESC, S.MEDICAL_COST_CATEGORY_2_DESC

,COUNT(SERV_LINE) OVER(PARTITION BY CLAIM_NBR ORDER BY SERV_LINE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) PREV

FROM vtClaims S

) T

 

;

 


Accepted Solutions
Teradata Employee

Re: Claims data - counting service lines with the same categories in 2 columns

Hi Karen, 

 

I uesd the forumla  # of times description is the same per claim / total services per claim in the below query based on your description of what the result set should look like. Not positive that this is what you're looking for as I'm not understanding the purpose of the PREV column in your subquery. If this isn't, please provide more input rows and an example of desired output

 

Hope this helps!

Michelle

 

CREATE MULTISET TABLE mt186048.vtClaims (
	 CLAIM_NBR CHAR(2)
	,SERV_LINE INT
	,MEDICAL_COST_CATEGORY_1_DESC VARCHAR(15)
	,MEDICAL_COST_CATEGORY_2_DESC VARCHAR(15)
)PRIMARY INDEX (CLAIM_NBR);

INSERT INTO mt186048.vtClaims ('x1',1,'ER','Other');
INSERT INTO mt186048.vtClaims ('x1',2,'Spec','Spec');
INSERT INTO mt186048.vtClaims ('x2',3,'ER','ER');

SELECT * FROM mt186048.vtClaims ORDER BY 1,2;
--CLAIM_NBR	SERV_LINE	1_DESC	2_DESC
--x1  		1		ER	Other
--x1  		2		Spec	Spec
--x2  		3		ER	ER

select
	CLAIM_NBR
	,SERV_LINE
	,MEDICAL_COST_CATEGORY_1_DESC
	,MEDICAL_COST_CATEGORY_2_DESC
--numerator: how many times descriptions are the same by claim ,SUM( CASE WHEN MEDICAL_COST_CATEGORY_1_DESC = MEDICAL_COST_CATEGORY_2_DESC THEN 1 ELSE 0 END ) OVER (PARTITION BY CLAIM_NBR) --avoid integer devision * 1.00 --denomiator: # of services / COUNT(*) OVER (PARTITION BY CLAIM_NBR) AS CLAIM_NBR_PCT_SAME FROM mt186048.vtClaims ORDER BY 1, 2 --CLAIM_NBR SERV_LINE 1_DESC 2_DESC CLAIM_NBR_PCT_SAME --x1 1 ER Other 0.50 --x1 2 Spec Spec 0.50 --x2 3 ER ER 1.00
1 ACCEPTED SOLUTION
3 REPLIES

Re: Claims data - counting service lines with the same categories in 2 columns

Sample_Answerset_KWallace.PNG

Teradata Employee

Re: Claims data - counting service lines with the same categories in 2 columns

Hi Karen, 

 

I uesd the forumla  # of times description is the same per claim / total services per claim in the below query based on your description of what the result set should look like. Not positive that this is what you're looking for as I'm not understanding the purpose of the PREV column in your subquery. If this isn't, please provide more input rows and an example of desired output

 

Hope this helps!

Michelle

 

CREATE MULTISET TABLE mt186048.vtClaims (
	 CLAIM_NBR CHAR(2)
	,SERV_LINE INT
	,MEDICAL_COST_CATEGORY_1_DESC VARCHAR(15)
	,MEDICAL_COST_CATEGORY_2_DESC VARCHAR(15)
)PRIMARY INDEX (CLAIM_NBR);

INSERT INTO mt186048.vtClaims ('x1',1,'ER','Other');
INSERT INTO mt186048.vtClaims ('x1',2,'Spec','Spec');
INSERT INTO mt186048.vtClaims ('x2',3,'ER','ER');

SELECT * FROM mt186048.vtClaims ORDER BY 1,2;
--CLAIM_NBR	SERV_LINE	1_DESC	2_DESC
--x1  		1		ER	Other
--x1  		2		Spec	Spec
--x2  		3		ER	ER

select
	CLAIM_NBR
	,SERV_LINE
	,MEDICAL_COST_CATEGORY_1_DESC
	,MEDICAL_COST_CATEGORY_2_DESC
--numerator: how many times descriptions are the same by claim ,SUM( CASE WHEN MEDICAL_COST_CATEGORY_1_DESC = MEDICAL_COST_CATEGORY_2_DESC THEN 1 ELSE 0 END ) OVER (PARTITION BY CLAIM_NBR) --avoid integer devision * 1.00 --denomiator: # of services / COUNT(*) OVER (PARTITION BY CLAIM_NBR) AS CLAIM_NBR_PCT_SAME FROM mt186048.vtClaims ORDER BY 1, 2 --CLAIM_NBR SERV_LINE 1_DESC 2_DESC CLAIM_NBR_PCT_SAME --x1 1 ER Other 0.50 --x1 2 Spec Spec 0.50 --x2 3 ER ER 1.00

Re: Claims data - counting service lines with the same categories in 2 columns

Michelle, Thank you so much!! Yes, this gets me what I need. Very grateful :)