050d1460-ed42-4360-8494-6d56bf8e8c73

.pdf
School
Northern Virginia Community College **We aren't endorsed by this school
Course
HIM 200
Subject
Business
Date
Nov 14, 2023
Pages
7
Uploaded by coca9999 on coursehero.com
This is a preview
Want to read all 7 pages? Go Premium today.
Already Premium? Sign in here
HAP-780 LM_10 Prepare testClaims data to predict high utilization defined as 50 or more claims in the 6-month period following the prediction time. The models should use 6 months of data prior to the prediction time as input. (6pt) Use sliding window approach with time step equal to one month. use HAP780 SELECT [patient_id] , min ( [claim_days] ) as min_days , max ( [claim_days] ) as max_days into #pat1 FROM [dbo] . [claims] group by patient_id having max ( [claim_days] ) - min ( [claim_days] ) > 360 --106226 rows affected drop table #pat1 SELECT * into #pat2 from #pat1 , [dbo] . [no] --3824136 rows affected drop table #pat2 SELECT * i #pat2 SELECT patient_id , min_days + ( no - 1 ) * 30 as pred_time from #pat2 where min_days + ( no - 1 ) * 30 > min_days + 180 and min_days + ( no - 1 ) * 30 > max_days - 180 order by no drop table #pat3 -- construct dependent variable select p . patient_id , pred_time , count (*) as claim_count , case when count (*) >= 50 then 1 else 0 end as highUtilizer into #dep from [dbo] . [claims] c , #pat3 p where p . patient_id = c . patient_id and c . cum_days > p . pred_time and c . cum_days <= p . pred_time + 180 group by p . patient_id , pred_time -- independent variables SELECT c . [patient_id] , pred_time , max ( case when [diagnosis] = 'ELIX1' then 1 else 0 end ) as ELIX1 This study source was downloaded by 100000773544593 from CourseHero.com on 10-27-2023 18:44:23 GMT -05:00 https://www.coursehero.com/file/176984367/LM-10docx/
, max ( case when [diagnosis] = 'ELIX10' then 1 else 0 end ) as ELIX10 , max ( case when [diagnosis] = 'ELIX11' then 1 else 0 end ) as ELIX11 , max ( case when [diagnosis] = 'ELIX12' then 1 else 0 end ) as ELIX12 , max ( case when [diagnosis] = 'ELIX13' then 1 else 0 end ) as ELIX13 , max ( case when [diagnosis] = 'ELIX14' then 1 else 0 end ) as ELIX14 , max ( case when [diagnosis] = 'ELIX15' then 1 else 0 end ) as ELIX15 , max ( case when [diagnosis] = 'ELIX16' then 1 else 0 end ) as ELIX16 , max ( case when [diagnosis] = 'ELIX17' then 1 else 0 end ) as ELIX17 , max ( case when [diagnosis] = 'ELIX18' then 1 else 0 end ) as ELIX18 , max ( case when [diagnosis] = 'ELIX19' then 1 else 0 end ) as ELIX19 , max ( case when [diagnosis] = 'ELIX2' then 1 else 0 end ) as ELIX2 , max ( case when [diagnosis] = 'ELIX20' then 1 else 0 end ) as ELIX20 , max ( case when [diagnosis] = 'ELIX21' then 1 else 0 end ) as ELIX21 , max ( case when [diagnosis] = 'ELIX22' then 1 else 0 end ) as ELIX22 , max ( case when [diagnosis] = 'ELIX23' then 1 else 0 end ) as ELIX23 , max ( case when [diagnosis] = 'ELIX24' then 1 else 0 end ) as ELIX24 , max ( case when [diagnosis] = 'ELIX25' then 1 else 0 end ) as ELIX25 , max ( case when [diagnosis] = 'ELIX26' then 1 else 0 end ) as ELIX26 , max ( case when [diagnosis] = 'ELIX27' then 1 else 0 end ) as ELIX27 , max ( case when [diagnosis] = 'ELIX28' then 1 else 0 end ) as ELIX28 , max ( case when [diagnosis] = 'ELIX29' then 1 else 0 end ) as ELIX29 , max ( case when [diagnosis] = 'ELIX3' then 1 else 0 end ) as ELIX3 , max ( case when [diagnosis] = 'ELIX4' then 1 else 0 end ) as ELIX4 , max ( case when [diagnosis] = 'ELIX5' then 1 else 0 end ) as ELIX5 , max ( case when [diagnosis] = 'ELIX6' then 1 else 0 end ) as ELIX6 , max ( case when [diagnosis] = 'ELIX7' then 1 else 0 end ) as ELIX7 , max ( case when [diagnosis] = 'ELIX8' then 1 else 0 end ) as ELIX8 , max ( case when [diagnosis] = 'ELIX9' then 1 else 0 end ) as ELIX9 into #Elix FROM [dbo] . [diagnoses] d , [dbo] . [claims] c , #pat3 as p where d . claim_id = c . claim_id and c . cum_days < p . pred_time and c . cum_days >= p . pred_time - 180 and p . patient_id = c . patient_id group by c . patient_id , pred_time select #Elix .*, claim_count , highUtilizer into [dbo] . [highUtilizationSlidingWindow] from #Elix , #dep where #elix . patient_id = #dep . patient_id and #elix . pred_time = #dep . pred_time select distinct patient_id into #pp from [highUtilizationSlidingWindow] select top 80 percent * into #train_pp from #pp -- (77403 rows affected) select * into [highUtilizationSlidingWindow_train] from [dbo] . [highUtilizationSlidingWindow] where patient_id in ( select * from #train_pp ) This study source was downloaded by 100000773544593 from CourseHero.com on 10-27-2023 18:44:23 GMT -05:00 https://www.coursehero.com/file/176984367/LM-10docx/
-- (1206754 rows affected select * into [highUtilizationSlidingWindow_test] from [highUtilizationSlidingWindow] where patient_id not in ( select * from #train_pp ) -- (302043 rows affected) This study source was downloaded by 100000773544593 from CourseHero.com on 10-27-2023 18:44:23 GMT -05:00 https://www.coursehero.com/file/176984367/LM-10docx/
Why is this page out of focus?
Because this is a Premium document. Subscribe to unlock this document and more.
Page1of 7
Uploaded by coca9999 on coursehero.com