DROP TABLE IF EXISTS mdc_appdev_lifeflux.mdc_flx_events_procedures_icd;

       CREATE TABLE mdc_appdev_lifeflux.mdc_flx_events_procedures_icd
       PARTITIONED BY (internalpatientid)
       STORED AS PARQUET
       AS
        select insertDate, dateOfEvent, eventXML, internalpatientid
        from (
        SELECT 
                            now() insertDate, 
                            procedures_icd.startdate dateOfEvent, 
                            CONCAT
                            (
                            '<Ev ',
                                'Type="',  'procedures_icd', '" ',
                                'Alias="','procedures_icd', '" ',
                                'Age="', cast(procedures_icd.ageatevent as varchar(23)), '" ',
                                'Time="', cast(procedures_icd.startdate as varchar(23)), '">',
                                '<Pr Name="ageatevent" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.ageatevent as string),'Null'), '</Pr>','<Pr Name="servicecode" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.servicecode as string),'Null'), '</Pr>','<Pr Name="insert_date" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.insert_date as string),'Null'), '</Pr>','<Pr Name="internalpatientid" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.internalpatientid as string),'Null'), '</Pr>','<Pr Name="startdate" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.startdate as string),'Null'), '</Pr>','<Pr Name="sequencenumber" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.sequencenumber as string),'Null'), '</Pr>','<Pr Name="servicecodedesc" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.servicecodedesc as string),'Null'), '</Pr>',
                            '</Ev>'
                            ) eventXML,
                            procedures_icd.internalpatientid internalpatientId 
                        FROM
                            mimic_db.procedures_icd INNER JOIN mimic_db.demographics_static
                            ON (mimic_db.procedures_icd.internalpatientid=mimic_db.demographics_static.internalpatientid)    
        ) tbl;