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


Reply
 
Thread Tools Search this Thread Display Modes
  #41  
Old 05-01-2019, 02:40 PM
Meshuga's Avatar
Meshuga Meshuga is offline
Member
Non-Actuary
 
Join Date: Dec 2001
Posts: 12,776
Default

i added a few extra codes for 31599377 for testing and left them in below

Code:
set nocount on

create table #clms (claim_sk varchar(8), proc_code varchar(5), mod_code varchar(2))
insert into #clms(claim_sk, proc_code, mod_code) values ('31599203', '98941', '')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599287', '98940', '')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599300', '97110', 'GO')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599301', '97140', 'GO')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599302', '97035', 'GO')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599303', '97014', 'GO')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599341', '10060', '')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599342', '99204', '25')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599375', '97110', 'GP')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599376', '97116', '59')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599376', '97116', 'GP')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599377', '97140', 'GP')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599377', '97140', 'XX')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599377', '97140', 'YY')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599377', '97140', 'ZZ')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599378', '97530', '59')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599378', '97530', 'GP')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599424', '99211', '')
insert into #clms(claim_sk, proc_code, mod_code) values ('31599425', '76816', '')

declare @num_codes integer;

select @num_codes=max(cnt) from (select claim_sk, proc_code, count(*) as cnt from #clms group by claim_sk, proc_code) a

declare @i integer;
set @i=0;
declare @sql varchar(1000);
set @sql = 'with ctmCodes as (
Select 
claim_sk
,proc_code
,row_number() over (partition by claim_sk  order by claim_sk, proc_code) as row_num
, mod_code
from #clms) SELECT claim_sk,proc_code'
while @i<@num_codes
  begin
    set @i = @i + 1
    set @sql=@sql+', isnull([' + cast((@i) as varchar(4)) + '],'''') as mod_code' + cast((@i) as varchar(4))
  end
  
set @sql=@sql+' FROM (SELECT claim_sk, proc_code, row_num, mod_code FROM ctmCodes) AS SrcTbl '
set @sql=@sql+'PIVOT (min(mod_code) FOR row_num IN ('
set @i=0;
while @i<@num_codes
  begin
    set @i = @i + 1
    set @sql=@sql+'[' + cast((@i) as varchar(4)) + ']' + (case when (@i<@num_codes) then ', ' else ')' end)
  end
 set @sql=@sql+ ') AS PivotTable;';

execute(@sql);

set nocount off 

drop table #clms

claim_sk	proc_code	mod_code1	mod_code2	mod_code3	mod_code4
31599203	98941				
31599287	98940					
31599300	97110		GO			
31599301	97140		GO			
31599302	97035		GO			
31599303	97014		GO			
31599341	10060				
31599342	99204		25			
31599375	97110		GP		GP		
31599376	97116		59		GP		
31599377	97140		GP		XX		YY		ZZ
31599378	97530		59		GP		
31599424	99211				
31599425	76816


i need to save this for future use
__________________
I know I don't talk in my sleep. Someone at work would have told me by now.

Last edited by Meshuga; 05-02-2019 at 11:11 AM..
Reply With Quote
  #42  
Old 05-01-2019, 05:21 PM
Helena Lake's Avatar
Helena Lake Helena Lake is offline
Member
SOA AAA
 
Join Date: Jan 2019
Studying for Nothing ever again!
Favorite beer: Whisky
Posts: 6,203
Default

CREATE TABLE BaseClaimProc1 AS
SELECT
claim_sk,
proc_code,
MAX(mod_code) AS mod_code1
FROM table1
GROUP BY
claim_sk,
proc_code;

CREATE TABLE MissingModsFor2 AS
SELECT
A.claim_sk,
B.proc_code
B.mod_code

FROM table1 A
LEFT JOIN BaseClaimProc1 B
ON A.claim_sk = B.claim_sk
AND A.proc_code = B.proc_code
AND A.mod_code = B.mod_code1

WHERE
B.mod_code1 IS NULL


CREATE TABLE BaseClaimProc2 AS
SELECT
A.claim_sk,
A.proc_code,
A. mod_code1,
MAX(B.mod_code) AS mod_code2

FROM BaseClaimProc1 A
LEFT JOIN MissingModsFor2
ON A.claim_sk = B.claim_sk
AND A.proc_code = B.proc_code

GROUP BY
A.claim_sk,
A.proc_code,
A. mod_code1



Repeat the last two steps a couple of times, until you've got as many mod_cd columns as you need. If you're feeling really adventurous, and MySQL qill let you write stored procedures, you could probably use the max # of modifiers in your data set as the limit on a loop that calls the other functions, and passes the current mod# in. What I have above is not very sophisticated, but it will probably work just fine, and isn't particularly complicated.

Apologies for variances in syntax, I've been using Netezza SQL for many years and it's got some differences. IIRC, some SQL variants have a "FIRST" function that would probably be a better choice than "MAX" for a text field.
__________________
Quote:
Originally Posted by BernieSanders2016 View Post
I don't think calling beliefs absurd and bullshit is criticism, it's insulting.
Reply With Quote
  #43  
Old 05-01-2019, 09:33 PM
Meshuga's Avatar
Meshuga Meshuga is offline
Member
Non-Actuary
 
Join Date: Dec 2001
Posts: 12,776
Default

ms sql has some things not available in nzsql. there would be no easy way to do this on netezza since you don't have variables and can't create dynamic sql unless you're using a stored procedure.

the only problem with your sql is, like you said, repeat the last two steps for each mod_code that he has. with the dynamic sql that is not a concern as you can perform a count to find the number of codes and then loop to create a field for the number of codes you have,
__________________
I know I don't talk in my sleep. Someone at work would have told me by now.
Reply With Quote
  #44  
Old 05-01-2019, 10:40 PM
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

The code I attempted to write earlier did not work exactly as I thought it should. Below is what I meant to write and I finally had a second to run it in an actual instance of SQL Server. The top of the code is a quick and dirty way to load your data as written in your post above.

Online SQL Server tool: https://rextester.com/l/sql_server_online_compiler

Code:
SELECT '31599287' as claim_sk,'98940' as proc_code,CAST('' as Char(2)) as mod_code INTO #claim_data;

INSERT INTO #claim_data SELECT '31599287','98940','';
INSERT INTO #claim_data SELECT '31599300','97110','GO';
INSERT INTO #claim_data SELECT '31599301','97140','GO';
INSERT INTO #claim_data SELECT '31599302','97035','GO';
INSERT INTO #claim_data SELECT '31599303','97014','GO';
INSERT INTO #claim_data SELECT '31599341','10060','';
INSERT INTO #claim_data SELECT '31599342','99204','25';
INSERT INTO #claim_data SELECT '31599375','97110','GP';
INSERT INTO #claim_data SELECT '31599376','97116','59';
INSERT INTO #claim_data SELECT '31599376','97116','GP';
INSERT INTO #claim_data SELECT '31599377','97140','GP';
INSERT INTO #claim_data SELECT '31599378','97530','59';
INSERT INTO #claim_data SELECT '31599378','97530','GP';
INSERT INTO #claim_data SELECT '31599424','99211','';
INSERT INTO #claim_data SELECT '31599425','76816','';

SELECT '' as [See, your data],* FROM #claim_data;

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
    #claim_data as A
    LEFT OUTER JOIN
    #claim_data as B
    ON
    A.claim_sk=B.claim_sk AND A.proc_code=B.proc_code AND A.mod_code<B.mod_code
GROUP BY
    A.claim_sk,A.proc_code,A.mod_code
)
,MyBigTable AS
(
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
 )
 SELECT
     B.*
 FROM
     (
     SELECT
             claim_sk,proc_code,Min(mod_code1) as mod_code1
     FROM
          MyBigTable
     GROUP BY
             claim_sk,proc_code
     ) as A
     INNER JOIN
     MyBigTable as B
     ON
     A.claim_sk=B.claim_sk AND A.proc_code=B.proc_code AND A.mod_code1=B.mod_code1

Last edited by rgreenlee; 05-02-2019 at 12:12 AM..
Reply With Quote
  #45  
Old 05-02-2019, 11:08 AM
Meshuga's Avatar
Meshuga Meshuga is offline
Member
Non-Actuary
 
Join Date: Dec 2001
Posts: 12,776
Default

Quote:
Originally Posted by rgreenlee View Post
The code I attempted to write earlier did not work exactly as I thought it should. Below is what I meant to write and I finally had a second to run it in an actual instance of SQL Server. The top of the code is a quick and dirty way to load your data as written in your post above.
i like your sql but you're maxing out at 4 mod_codes, where he says above there are 200ish
__________________
I know I don't talk in my sleep. Someone at work would have told me by now.
Reply With Quote
  #46  
Old 05-02-2019, 11:39 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

After some massaging I only need 12 of the mod codes. 26, TC, NU, RR and 4 Anesthesia ones, for example. The rest I just said were "global" and considered them as blanks.
There are 200 different Mod Codes, however only a max of 4 on the same claim (claim line). so I think the above works fine.
__________________
Quote:
Originally Posted by ORLYLOL View Post
Beer is ****ing amazing

Last edited by T-roy; 05-02-2019 at 11:45 AM..
Reply With Quote
  #47  
Old 05-02-2019, 12:48 PM
Steve Grondin Steve Grondin is offline
Member
SOA AAA
 
Join Date: Nov 2001
Posts: 6,784
Default

This has been tickling my brain for a bit. Reminds me of when I first started SQL and was having difficulty transitioning thinking about how to do stuff in excel into SQL.

It seems you want the number of new columns to the max of the count of duplicates of claim_sk, proc_code. The "occurrence number" would be the column of the mod_code.

In Excel, I'd sort and run a counter that resets to 1 at a new occurrence of the key to get the occurrence number.

I think in SQL, you could inner join on claim_sk=claim_sk, proc_code=proc_code, and mod_code <= mod_code. I think the count of the matched records would be the occurrence number. IIRC our mutual friend taught me this trick. Then you could pivot on the occurrence number and assign the max(mod_code) {since there is only one mod_code per occurrence number} to the value.
Reply With Quote
  #48  
Old 05-02-2019, 04:08 PM
Steve Grondin Steve Grondin is offline
Member
SOA AAA
 
Join Date: Nov 2001
Posts: 6,784
YouTube

Sweet, it works.

Using Meshuga's 19 row dataset above:

Code:
SELECT Claim_sk, proc_code, [1], [2], [3], [4] FROM
((SELECT A.Claim_sk, A.proc_code, a.mod_code, count(b.mod_code) AS mod_code_CNT FROM 
(SELECT * FROM dbo.SGTemp) AS A
INNER JOIN 
(SELECT * FROM dbo.SGTemp) AS B
ON
A.Claim_SK = B.Claim_SK and A.proc_code = B.proc_code And A.mod_code >= B.mod_code
GROUP BY A.Claim_sk, A.proc_code, a.mod_code)) AS C
PIVOT
( 
MAX(C.mod_code)
FOR 
mod_code_cnt
IN ([1], [2], [3], [4])
 ) AS Tada
gives
Code:
Claim_sk	proc_code	1	2	3	4
31599341	    10060    	  	NULL	NULL	NULL
31599425	    76816	  	NULL	NULL	NULL
31599303	    97014	GO	NULL	NULL	NULL
31599302	    97035	GO	NULL	NULL	NULL
31599300	    97110	GO	NULL	NULL	NULL
31599375	    97110	GP	NULL	NULL	NULL
31599376	    97116	59	GP	NULL	NULL
31599301	    97140	GO	NULL	NULL	NULL
31599377	    97140	GP	XX	YY	ZZ
31599378	    97530	59	GP	NULL	NULL
31599287	    98940	  	NULL	NULL	NULL
31599203	    98941	  	NULL	NULL	NULL
31599342	    99204	25	NULL	NULL	NULL
31599424	    99211	  	NULL	NULL	NULL
Note that behavior is different if the zero length strings in the input data are NULLs. Records with NULL in mod_code are dropped from results set.
Reply With Quote
  #49  
Old 05-02-2019, 04:36 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

Nawce! Had to fix a few items but I got something running.
__________________
Quote:
Originally Posted by ORLYLOL View Post
Beer is ****ing amazing
Reply With Quote
  #50  
Old 05-02-2019, 06:04 PM
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

Definitely like the pivot method, never used it before
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 11:20 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.20966 seconds with 9 queries