lambda/HiveTable_Rush Memorial.txt at main · ecjackson1821/lambda · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
create external table rumc_charges(CodeType string, CodeID string, Code string, NDC string, CodeDescription string, GrossCharge decimal, DeIdentifiedMinimumNegotiatedCharge decimal, DeIdentifiedMaximumNegotiatedCharge decimal, DiscountedCashPrice decimal, AETNAINTERNATIONAL_501036 decimal, AETNASIGNATUREADMINISTRATORS_501021 decimal, AETNASTATEOFILLINOISPPO_HMO_OAP_501110 decimal, AFFILIATESCIGNA_517026 decimal, ALLIEDBENEFITAETNASIGNATURE_501025 decimal, BASIC_PREMIERRUSHEMPALLEGIANCE_GWH_CIGNA_517020 decimal, BDCTBLUEDISTINCTIONCENTERSFORTRANSPLANT_510023 decimal, BLUECAREDIRECTBCHMOI_510142 decimal, BLUECHOICEOPTIONSBCO_BLUEOPTIONSPPO_510141 decimal, BLUEPRECISIONBCHMOI_510143 decimal, CIGNABEHAVIORALHEALTH_517022 decimal, CIGNACTAEMPLOYEES_517025 decimal, CIGNAONEHEALTHHMO_517030 decimal, CORESOURCEAETNASIGNATURE_501028 decimal, GENERICIPA_HMOIBLUECROSS_545010 decimal, GOLDENRULEUNITEDHEALTHCARE_582023 decimal, HMOAETNA_OPENACCESS_501010 decimal,HMOANTHEMBC_510024 decimal, HMOOUTOFSTATEBC_510016 decimal, HMO_NETWORKCIGNA_517010 decimal, HMOHUMANA_540013 decimal, HMOIBLUECROSS_510134 decimal, MAILHANDLERSAETNA_501044 decimal, OXFORDUNITEDHEALTHCARE_582024 decimal, POSCHOICEHUMANA_540022 decimal, POS_EPOAETNA_501013 decimal, POS_POSPLUSCIGNA_517013 decimal, PPOAETNA_501014 decimal, PPOAETNAILLINOISPREFERRED_501032 decimal, PPOANTHEMBC_510025 decimal, PPOFIRSTHEALTH_526010 decimal, PPOHFN10_536010 decimal, PPOHUMANA_540015 decimal, PPOILLINOISBC_510133 decimal, PPOMULTIPLAN_555710 decimal, PPOOUTOFSTATEBC_510018 decimal, PPOPHCS_563012 decimal, PPOUNITEDHEALTHCARECHOICE_SELECT_OPTION_582010 decimal, PPO_EPO_OPENACCESS_NETWORK_PLUSCIGNA_517012 decimal, PPO_OPENACCESSPLUSGREATWEST_GWH_CIGNA_532012 decimal, SELECTEPORUSHEMPLOYEEALLEGIANCE_GWH_CIGNA_517021 decimal, SPECIALTYPHYSICIANSOFILLINOIS_578110 decimal, STUDENTRESOURCESPPOUNITEDHEALTHCARE_582027 decimal, TRANSPLANTCIGNA_PPO_LIFESOURCE_517016 decimal, UHCAFFILIATES_582025 decimal, UMRUNITEDHEALTHCARE_582022 decimal, UNITEDCORE_NAVIGATE_582021 decimal)
row format delimited fields terminated by ',' stored as textfile location  '/tmp/ecjackson/project/rumc'

CREATE table rush_min_max (
    hospital string, code string, code_description string, min_charge double, insurer_min_charge string, max_charge double, insurer_max_charge string, uninsured_charge double) stored as orc;


INSERT OVERWRITE table rush_min_max
SELECT 'Rush Memorial', r.Code, r.CodeDescription, r.DeIdentifiedMinimumNegotiatedCharge as MinCharge,
        (CASE least(r.AETNAINTERNATIONAL_501036, r.AETNASTATEOFILLINOISPPO_HMO_OAP_501110, AFFILIATESCIGNA_517026, ALLIEDBENEFITAETNASIGNATURE_501025, BASIC_PREMIERRUSHEMPALLEGIANCE_GWH_CIGNA_517020, BDCTBLUEDISTINCTIONCENTERSFORTRANSPLANT_510023, BLUECAREDIRECTBCHMOI_510142, BLUECHOICEOPTIONSBCO_BLUEOPTIONSPPO_510141, BLUEPRECISIONBCHMOI_510143, CIGNABEHAVIORALHEALTH_517022, CIGNACTAEMPLOYEES_517025, CIGNAONEHEALTHHMO_517030, CORESOURCEAETNASIGNATURE_501028, GENERICIPA_HMOIBLUECROSS_545010, GOLDENRULEUNITEDHEALTHCARE_582023, HMOAETNA_OPENACCESS_501010,HMOANTHEMBC_510024, HMOOUTOFSTATEBC_510016, HMO_NETWORKCIGNA_517010, HMOHUMANA_540013, HMOIBLUECROSS_510134, MAILHANDLERSAETNA_501044, OXFORDUNITEDHEALTHCARE_582024, POSCHOICEHUMANA_540022, POS_EPOAETNA_501013, POS_POSPLUSCIGNA_517013, PPOAETNA_501014, PPOAETNAILLINOISPREFERRED_501032, PPOANTHEMBC_510025, PPOFIRSTHEALTH_526010, PPOHFN10_536010, PPOHUMANA_540015, PPOILLINOISBC_510133, PPOMULTIPLAN_555710, PPOOUTOFSTATEBC_510018, PPOPHCS_563012, PPOUNITEDHEALTHCARECHOICE_SELECT_OPTION_582010, PPO_EPO_OPENACCESS_NETWORK_PLUSCIGNA_517012, PPO_OPENACCESSPLUSGREATWEST_GWH_CIGNA_532012, SELECTEPORUSHEMPLOYEEALLEGIANCE_GWH_CIGNA_517021, SPECIALTYPHYSICIANSOFILLINOIS_578110, STUDENTRESOURCESPPOUNITEDHEALTHCARE_582027, TRANSPLANTCIGNA_PPO_LIFESOURCE_517016, UHCAFFILIATES_582025, UMRUNITEDHEALTHCARE_582022, UNITEDCORE_NAVIGATE_582021)
              WHEN r.AETNAINTERNATIONAL_501036 THEN 'Aetna International'
              WHEN r.AETNASTATEOFILLINOISPPO_HMO_OAP_501110 THEN 'Aetna IL PPO/HMO'
              WHEN r.AFFILIATESCIGNA_517026 THEN 'Cigna Affiliates'
              WHEN r.ALLIEDBENEFITAETNASIGNATURE_501025 THEN 'Aetna Signature Allied Benefit'
              WHEN r.BASIC_PREMIERRUSHEMPALLEGIANCE_GWH_CIGNA_517020 THEN 'Cigna Basic Premier Rush Emp Allegiance'
              WHEN r.BDCTBLUEDISTINCTIONCENTERSFORTRANSPLANT_510023 THEN 'Blue Distinction Centers for Transplant'
              WHEN r.BLUECAREDIRECTBCHMOI_510142 THEN 'Blue Care Direct HMOI'
              WHEN r.BLUECHOICEOPTIONSBCO_BLUEOPTIONSPPO_510141 THEN 'Blue Choice Options BCO/PPO'
              WHEN r.BLUEPRECISIONBCHMOI_510143 THEN 'Blue Precision BC HMOI'
              WHEN r.CIGNABEHAVIORALHEALTH_517022 THEN 'Cigna Behavioral Health'
              WHEN r.CIGNACTAEMPLOYEES_517025 THEN 'Cigna CTA Employees'
              WHEN r.CIGNAONEHEALTHHMO_517030 THEN 'Cigna One Health HMO'
              WHEN r.CORESOURCEAETNASIGNATURE_501028 THEN 'Aetna Signature - Core Source'
              WHEN r.GENERICIPA_HMOIBLUECROSS_545010 THEN 'Blue Cross - Generic IPA / HMOI'
              WHEN r.GOLDENRULEUNITEDHEALTHCARE_582023 THEN 'United Healthcare - Golden Rule'
              WHEN r.HMOAETNA_OPENACCESS_501010 THEN 'Aetna Open Access HMO'
              WHEN r.HMOANTHEMBC_510024 THEN 'Anthem BC - HMO'
              WHEN r.HMOOUTOFSTATEBC_510016 THEN 'Out of State Blue Cross - HMO'
              WHEN r.HMO_NETWORKCIGNA_517010 THEN 'Cigna - HMO/Network'
              WHEN r.HMOHUMANA_540013 THEN 'Humana - HMO'
              WHEN r.HMOIBLUECROSS_510134 THEN 'Blue Cross - HMOI'
              WHEN r.MAILHANDLERSAETNA_501044 THEN 'Aetna Mail Handlers'
              WHEN r.OXFORDUNITEDHEALTHCARE_582024 THEN 'United Healthcare, Oxford'
              WHEN r.POSCHOICEHUMANA_540022 THEN 'Humana - POS Choice'
              WHEN r.POS_EPOAETNA_501013 THEN 'Aetna - POS/EPO'
              WHEN r.POS_POSPLUSCIGNA_517013 THEN 'Cigna - POS/POS Plus'
              WHEN r.PPOAETNA_501014 THEN 'Aetna PPO'
              WHEN r.PPOAETNAILLINOISPREFERRED_501032 THEN 'Aetna IL Preferred - PPO'
              WHEN r.PPOANTHEMBC_510025 THEN 'Anthem BC - PPO'
              WHEN r.PPOFIRSTHEALTH_526010 THEN 'First Health PPO'
              WHEN r.PPOHFN10_536010 THEN 'HFN 10 - PPO'
              WHEN r.PPOHUMANA_540015 THEN 'Humana - PPO'
              WHEN r.PPOILLINOISBC_510133 THEN 'Illinois Blue Cross - PPO'
              WHEN r.PPOMULTIPLAN_555710 THEN 'MultiPlan - PPO'
              WHEN r.PPOOUTOFSTATEBC_510018 THEN 'Out of State Blue Cross - PPO'
              WHEN r.PPOPHCS_563012 THEN 'PHCS - PPO'
              WHEN r.PPOUNITEDHEALTHCARECHOICE_SELECT_OPTION_582010 THEN 'United Healthcare Choice/Select/Option - PPO'
              WHEN r.PPOUNITEDHEALTHCARECHOICE_SELECT_OPTION_582010 THEN 'Cigna - PPO/EPO/Open Access/Network/Plus'
              WHEN r.PPO_OPENACCESSPLUSGREATWEST_GWH_CIGNA_532012 THEN 'Great West/GWH/Cigna - PPO/Open Access Plus'
              WHEN r.SELECTEPORUSHEMPLOYEEALLEGIANCE_GWH_CIGNA_517021 THEN 'GWH/Cigna - Rush Employee Allegiance '
              WHEN r.SPECIALTYPHYSICIANSOFILLINOIS_578110 THEN 'Specialty Physicians of Illinois'
              WHEN r.STUDENTRESOURCESPPOUNITEDHEALTHCARE_582027 THEN 'United Healthcare - Student Resources PPO'
              WHEN r.TRANSPLANTCIGNA_PPO_LIFESOURCE_517016 THEN 'Cigna/LifeSource/PPO - Transplant'
              WHEN r.UHCAFFILIATES_582025 THEN 'UHC Affiliates'
              WHEN r.UMRUNITEDHEALTHCARE_582022 THEN 'United Healthcare - UMR'
              WHEN r.UNITEDCORE_NAVIGATE_582021 THEN 'United Core/Navigate' END) as InsurerMinCharge, r.DeIdentifiedMaximumNegotiatedCharge as MaxCharge,
                    (CASE greatest(r.AETNAINTERNATIONAL_501036, r.AETNASTATEOFILLINOISPPO_HMO_OAP_501110, AFFILIATESCIGNA_517026, ALLIEDBENEFITAETNASIGNATURE_501025, BASIC_PREMIERRUSHEMPALLEGIANCE_GWH_CIGNA_517020, BDCTBLUEDISTINCTIONCENTERSFORTRANSPLANT_510023, BLUECAREDIRECTBCHMOI_510142, BLUECHOICEOPTIONSBCO_BLUEOPTIONSPPO_510141, BLUEPRECISIONBCHMOI_510143, CIGNABEHAVIORALHEALTH_517022, CIGNACTAEMPLOYEES_517025, CIGNAONEHEALTHHMO_517030, CORESOURCEAETNASIGNATURE_501028, GENERICIPA_HMOIBLUECROSS_545010, GOLDENRULEUNITEDHEALTHCARE_582023, HMOAETNA_OPENACCESS_501010,HMOANTHEMBC_510024, HMOOUTOFSTATEBC_510016, HMO_NETWORKCIGNA_517010, HMOHUMANA_540013, HMOIBLUECROSS_510134, MAILHANDLERSAETNA_501044, OXFORDUNITEDHEALTHCARE_582024, POSCHOICEHUMANA_540022, POS_EPOAETNA_501013, POS_POSPLUSCIGNA_517013, PPOAETNA_501014, PPOAETNAILLINOISPREFERRED_501032, PPOANTHEMBC_510025, PPOFIRSTHEALTH_526010, PPOHFN10_536010, PPOHUMANA_540015, PPOILLINOISBC_510133, PPOMULTIPLAN_555710, PPOOUTOFSTATEBC_510018, PPOPHCS_563012, PPOUNITEDHEALTHCARECHOICE_SELECT_OPTION_582010, PPO_EPO_OPENACCESS_NETWORK_PLUSCIGNA_517012, PPO_OPENACCESSPLUSGREATWEST_GWH_CIGNA_532012, SELECTEPORUSHEMPLOYEEALLEGIANCE_GWH_CIGNA_517021, SPECIALTYPHYSICIANSOFILLINOIS_578110, STUDENTRESOURCESPPOUNITEDHEALTHCARE_582027, TRANSPLANTCIGNA_PPO_LIFESOURCE_517016, UHCAFFILIATES_582025, UMRUNITEDHEALTHCARE_582022, UNITEDCORE_NAVIGATE_582021)
              WHEN r.AETNAINTERNATIONAL_501036 THEN 'Aetna International'
              WHEN r.AETNASTATEOFILLINOISPPO_HMO_OAP_501110 THEN 'Aetna IL PPO/HMO'
              WHEN r.AFFILIATESCIGNA_517026 THEN 'Cigna Affiliates'
              WHEN r.ALLIEDBENEFITAETNASIGNATURE_501025 THEN 'Aetna Signature Allied Benefit'
              WHEN r.BASIC_PREMIERRUSHEMPALLEGIANCE_GWH_CIGNA_517020 THEN 'Cigna Basic Premier Rush Emp Allegiance'
              WHEN r.BDCTBLUEDISTINCTIONCENTERSFORTRANSPLANT_510023 THEN 'Blue Distinction Centers for Transplant'
              WHEN r.BLUECAREDIRECTBCHMOI_510142 THEN 'Blue Care Direct HMOI'
              WHEN r.BLUECHOICEOPTIONSBCO_BLUEOPTIONSPPO_510141 THEN 'Blue Choice Options BCO/PPO'
              WHEN r.BLUEPRECISIONBCHMOI_510143 THEN 'Blue Precision BC HMOI'
              WHEN r.CIGNABEHAVIORALHEALTH_517022 THEN 'Cigna Behavioral Health'
              WHEN r.CIGNACTAEMPLOYEES_517025 THEN 'Cigna CTA Employees'
              WHEN r.CIGNAONEHEALTHHMO_517030 THEN 'Cigna One Health HMO'
              WHEN r.CORESOURCEAETNASIGNATURE_501028 THEN 'Aetna Signature - Core Source'
              WHEN r.GENERICIPA_HMOIBLUECROSS_545010 THEN 'Blue Cross - Generic IPA / HMOI'
              WHEN r.GOLDENRULEUNITEDHEALTHCARE_582023 THEN 'United Healthcare - Golden Rule'
              WHEN r.HMOAETNA_OPENACCESS_501010 THEN 'Aetna Open Access HMO'
              WHEN r.HMOANTHEMBC_510024 THEN 'Anthem BC - HMO'
              WHEN r.HMOOUTOFSTATEBC_510016 THEN 'Out of State Blue Cross - HMO'
              WHEN r.HMO_NETWORKCIGNA_517010 THEN 'Cigna - HMO/Network'
              WHEN r.HMOHUMANA_540013 THEN 'Humana - HMO'
              WHEN r.HMOIBLUECROSS_510134 THEN 'Blue Cross - HMOI'
              WHEN r.MAILHANDLERSAETNA_501044 THEN 'Aetna Mail Handlers'
              WHEN r.OXFORDUNITEDHEALTHCARE_582024 THEN 'United Healthcare, Oxford'
              WHEN r.POSCHOICEHUMANA_540022 THEN 'Humana - POS Choice'
              WHEN r.POS_EPOAETNA_501013 THEN 'Aetna - POS/EPO'
              WHEN r.POS_POSPLUSCIGNA_517013 THEN 'Cigna - POS/POS Plus'
              WHEN r.PPOAETNA_501014 THEN 'Aetna PPO'
              WHEN r.PPOAETNAILLINOISPREFERRED_501032 THEN 'Aetna IL Preferred - PPO'
              WHEN r.PPOANTHEMBC_510025 THEN 'Anthem BC - PPO'
              WHEN r.PPOFIRSTHEALTH_526010 THEN 'First Health PPO'
              WHEN r.PPOHFN10_536010 THEN 'HFN 10 - PPO'
              WHEN r.PPOHUMANA_540015 THEN 'Humana - PPO'
              WHEN r.PPOILLINOISBC_510133 THEN 'Illinois Blue Cross - PPO'
              WHEN r.PPOMULTIPLAN_555710 THEN 'MultiPlan - PPO'
              WHEN r.PPOOUTOFSTATEBC_510018 THEN 'Out of State Blue Cross - PPO'
              WHEN r.PPOPHCS_563012 THEN 'PHCS - PPO'
              WHEN r.PPOUNITEDHEALTHCARECHOICE_SELECT_OPTION_582010 THEN 'United Healthcare Choice/Select/Option - PPO'
              WHEN r.PPOUNITEDHEALTHCARECHOICE_SELECT_OPTION_582010 THEN 'Cigna - PPO/EPO/Open Access/Network/Plus'
              WHEN r.PPO_OPENACCESSPLUSGREATWEST_GWH_CIGNA_532012 THEN 'Great West/GWH/Cigna - PPO/Open Access Plus'
              WHEN r.SELECTEPORUSHEMPLOYEEALLEGIANCE_GWH_CIGNA_517021 THEN 'GWH/Cigna - Rush Employee Allegiance '
              WHEN r.SPECIALTYPHYSICIANSOFILLINOIS_578110 THEN 'Specialty Physicians of Illinois'
              WHEN r.STUDENTRESOURCESPPOUNITEDHEALTHCARE_582027 THEN 'United Healthcare - Student Resources PPO'
              WHEN r.TRANSPLANTCIGNA_PPO_LIFESOURCE_517016 THEN 'Cigna/LifeSource/PPO - Transplant'
              WHEN r.UHCAFFILIATES_582025 THEN 'UHC Affiliates'
              WHEN r.UMRUNITEDHEALTHCARE_582022 THEN 'United Healthcare - UMR'
              WHEN r.UNITEDCORE_NAVIGATE_582021 THEN 'United Core/Navigate' END) as InsurerMaxCharge, r.DiscountedCashPrice as UnInsured FROM rumc_charges r;