Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Search Actuarial Jobs by State @ DWSimpson.com:
AL AK AR AZ CA CO CT DE FL GA HI ID IL IN IA KS KY LA
ME MD MA MI MN MS MO MT NE NH NJ NM NY NV NC ND
OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY

Reply
 
Thread Tools Search this Thread Display Modes
  #21  
Old 04-30-2019, 06:35 PM
T-roy's Avatar
T-roy T-roy is offline
Member
SOA
 
Join Date: May 2002
College: UW Whitewater
Favorite beer: FFF GBH/NG Moon Man
Posts: 8,867
Blog Entries: 1
Default

So this is a little shorter SQL question:

I want to turn this (specifically the bolded):

Code:
claim_sk proc_code mod_code
31599203 98941	
31599287 98940	
31599300 97110 GO
31599301 97140 GO
31599302 97035 GO
31599303 97014 GO
31599341 10060	
31599342 99204 25
31599375 97110 GP
31599376 97116 59
31599376 97116 GP 
31599377 97140 GP
31599378 97530 59
31599378 97530 GP
31599424 99211	
31599425 76816
Into this:

Code:
claim_sk proc_code mod_code1 mod_code2
31599203 98941	
31599287 98940	
31599300 97110 GO
31599301 97140 GO
31599302 97035 GO
31599303 97014 GO
31599341 10060	
31599342 99204 25
31599375 97110 GP
31599376 97116 59 GP 
31599377 97140 GP
31599378 97530 59 GP
31599424 99211	
31599425 76816
My claim system doesn't hold CPT and their Modifiers in the same file. So I have to attach them, however, mod codes will duplicate the claim because of the first table. I am trying to keep all modifiers on the claim by just assigning them to mod_code2, mod_code3, etc. (I found this only has 3 modifiers max).
__________________
Quote:
Originally Posted by ORLYLOL View Post
Beer is ****ing amazing

Last edited by T-roy; 04-30-2019 at 06:44 PM..
Reply With Quote
  #22  
Old 04-30-2019, 06:52 PM
Mathdube2 Mathdube2 is offline
Member
SOA
 
Join Date: Oct 2018
Posts: 40
Default

Quote:
Originally Posted by T-roy View Post
So this is a little shorter SQL question:

I want to turn this (specifically the bolded):

Code:
claim_sk proc_code mod_code
31599203 98941	
31599287 98940	
31599300 97110 GO
31599301 97140 GO
31599302 97035 GO
31599303 97014 GO
31599341 10060	
31599342 99204 25
31599375 97110 GP
31599376 97116 59
31599376 97116 GP 
31599377 97140 GP
31599378 97530 59
31599378 97530 GP
31599424 99211	
31599425 76816
Into this:

Code:
claim_sk proc_code mod_code1 mod_code2
31599203 98941	
31599287 98940	
31599300 97110 GO
31599301 97140 GO
31599302 97035 GO
31599303 97014 GO
31599341 10060	
31599342 99204 25
31599375 97110 GP
31599376 97116 59 GP 
31599377 97140 GP
31599378 97530 59 GP
31599424 99211	
31599425 76816
My claim system doesn't hold CPT and their Modifiers in the same file. So I have to attach them, however, mod codes will duplicate the claim because of the first table. I am trying to keep all modifiers on the claim by just assigning them to mod_code2, mod_code3, etc. (I found this only has 3 modifiers max).
Could use concatenation and a group by when you join
Reply With Quote
  #23  
Old 04-30-2019, 07:01 PM
T-roy's Avatar
T-roy T-roy is offline
Member
SOA
 
Join Date: May 2002
College: UW Whitewater
Favorite beer: FFF GBH/NG Moon Man
Posts: 8,867
Blog Entries: 1
Default

I have one table with the claim_sk (31599376 for example)

The procedure and mod table has
Code:
 claim_sk proc_code mod_code
31599376 97116 59
31599376 97116 GP
Which I'd like to turn into this before I join to the claim table:
Code:
 claim_sk proc_code mod_code1 mod_code2
31599376 97116 59 GP
__________________
Quote:
Originally Posted by ORLYLOL View Post
Beer is ****ing amazing
Reply With Quote
  #24  
Old 04-30-2019, 07:11 PM
Mathdube2 Mathdube2 is offline
Member
SOA
 
Join Date: Oct 2018
Posts: 40
Default

Do you use SAS?
Reply With Quote
  #25  
Old 04-30-2019, 09:32 PM
Knoath Knoath is offline
Member
CAS
 
Join Date: Oct 2015
Posts: 89
Default

Quote:
Originally Posted by T-roy View Post
So this is a little shorter SQL question:

I want to turn this (specifically the bolded):

Code:
claim_sk proc_code mod_code
31599203 98941	
31599287 98940	
31599300 97110 GO
31599301 97140 GO
31599302 97035 GO
31599303 97014 GO
31599341 10060	
31599342 99204 25
31599375 97110 GP
31599376 97116 59
31599376 97116 GP 
31599377 97140 GP
31599378 97530 59
31599378 97530 GP
31599424 99211	
31599425 76816
Into this:

Code:
claim_sk proc_code mod_code1 mod_code2
31599203 98941	
31599287 98940	
31599300 97110 GO
31599301 97140 GO
31599302 97035 GO
31599303 97014 GO
31599341 10060	
31599342 99204 25
31599375 97110 GP
31599376 97116 59 GP 
31599377 97140 GP
31599378 97530 59 GP
31599424 99211	
31599425 76816
My claim system doesn't hold CPT and their Modifiers in the same file. So I have to attach them, however, mod codes will duplicate the claim because of the first table. I am trying to keep all modifiers on the claim by just assigning them to mod_code2, mod_code3, etc. (I found this only has 3 modifiers max).
If there's only two values you could use ordered analytics -

select * from
(Select
claim_sk
,proc_code
,min(mod_code) over (partition by claim_sk,proc_code) mod_code1
,max(mod_code) over (partition by claim_sk,proc_code) mod_code2
)dt
group by claim_sk,proc_code,mod_code1,mod_code2
Reply With Quote
  #26  
Old 05-01-2019, 08:53 AM
rgreenlee's Avatar
rgreenlee rgreenlee is offline
Member
Non-Actuary
 
Join Date: Jun 2014
Location: in a van down by the river
Posts: 1,075
Default

Code:
WITH MyOrderedPairs AS
(
SELECT
	A.claim_sk, A.proc_code
	,A.mod_code as mod_code1
	,Min(ISNULL(B.mod_code,'')) as mod_code2
FROM
	NeverToldMeTableName as A
	LEFT OUTER JOIN
	NeverToldMeTableName as B
	ON
	A.mod_code<B.mod_code
)
SELECT
	A.claim_sk,A.proc_code
	,A.mod_code1
	,ISNULL(B.mod_code1,'') as mod_code2
	,ISNULL(C.mod_code1,'') as mod_code3
	,ISNULL(C.mod_code2,'') as mod_code4
FROM
	MyOrderedPairs as A
	LEFT OUTER JOIN
	MyOrderedPairs as B
	ON
	A.claim_sk=B.claim_sk AND A.proc_code=B.proc_code AND A.mod_code2=B.mod_code1
	LEFT OUTER JOIN
	MyOrderedPairs as C
	ON
	A.claim_sk=C.claim_sk AND A.proc_code=C.proc_code AND B.mod_code2=C.mod_code1
This method assumes that you have no duplicates in the table NeverToldMeTableName.

More advanced solution: You can write a custom aggregate function (I did this at work) that will combine a column like this:

Diag_code
222
333
H22

combined in one column record to 222 333 H22

Last edited by rgreenlee; 05-01-2019 at 09:04 AM.. Reason: keep finding issues because i couldn't run this on my computer to test it
Reply With Quote
  #27  
Old 05-01-2019, 08:58 AM
rgreenlee's Avatar
rgreenlee rgreenlee is offline
Member
Non-Actuary
 
Join Date: Jun 2014
Location: in a van down by the river
Posts: 1,075
Default

Note that if you have lots of diagnosis codes, you can do this process with as many left outer joins
Reply With Quote
  #28  
Old 05-01-2019, 09:07 AM
rgreenlee's Avatar
rgreenlee rgreenlee is offline
Member
Non-Actuary
 
Join Date: Jun 2014
Location: in a van down by the river
Posts: 1,075
Default

I am going to stop editing, so the code as written may not work because there might be some issue I didn't see.

But idea is:
get a list of diag1,diag2 where diag2 is the first one that comes alphabetically after diag1.

keep joining and getting the next diagnosis code and adding it to the right. eventually you get them all with enough joins
Reply With Quote
  #29  
Old 05-01-2019, 09:19 AM
T-roy's Avatar
T-roy T-roy is offline
Member
SOA
 
Join Date: May 2002
College: UW Whitewater
Favorite beer: FFF GBH/NG Moon Man
Posts: 8,867
Blog Entries: 1
Default

Quote:
Originally Posted by rgreenlee View Post
Note that if you have lots of diagnosis codes, you can do this process with as many left outer joins
I miss SAS, only have SQL

There are 4 mod codes possible, although I think 3 is the maximum
__________________
Quote:
Originally Posted by ORLYLOL View Post
Beer is ****ing amazing
Reply With Quote
  #30  
Old 05-01-2019, 09:23 AM
T-roy's Avatar
T-roy T-roy is offline
Member
SOA
 
Join Date: May 2002
College: UW Whitewater
Favorite beer: FFF GBH/NG Moon Man
Posts: 8,867
Blog Entries: 1
Default

Quote:
Originally Posted by Mathdube2 View Post
Do you use SAS?
I wish, this'd be easy with SAS
__________________
Quote:
Originally Posted by ORLYLOL View Post
Beer is ****ing amazing
Reply With Quote
Reply

Tags
microsoft sql

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 10:57 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.41291 seconds with 9 queries