<commit_before>/*
Create data table for pregnency/delivery site.
State Hospital Survey Data available at https://dch.georgia.gov/health-planning-databases
(Annual Hospital Questionaire - 2000 to 2014)
*/
select b.uid, b.Medicare_Provider_No,
       c.HospitalName,
       a.Delivery_Rms,
       a.Birthing_Rms,
       a.LDR_Rms,
       a.LDRP_Rms,
       a.C_Sect,
       a.Live_Births,
       a.Total_Births,
       a.Total_Deliveries,
       a.Beds_New_Born,
       a.Beds_Inter7mediate,
       a.Beds_Intensive,
       a.Adms_New_Born,
       a.Adms_Intermediate,
       a.Adms_Intensive,
       a.Days_New_Born,
       a.Days_Intermediate,
       a.Days_Intensive,
       round(a.C_Sect / a.Total_Births * 100, 0) as csection_pct,
       a.Avg_Delivery_Charge,
       a.Avg_Premature_Delivery_Charge,
       d.medicare_births,
       d.early_births,
       d.footnote
from ahq.perinatal a
join ahq.lu_id b using(uid)
join hospital_compare.hospital_names c
  on c.providerNumber = b.Medicare_Provider_No
left join (
  select provider_id,
         score as early_births,
         sample as medicare_births,
         footnote
  from hospital_compare.HQI_HOSP_TimelyEffectiveCare
  where measure_id = 'PC_01'
) d on b.Medicare_Provider_No = d.provider_id
where a.year = 2013
  and a.Total_Deliveries > 0
having a.C_Sect / a.Total_Births < 1<commit_msg>Create perinatal table for birth/delivery site<commit_after>/*
Create perinatal data table for pregnency/delivery site.
Data available at https://dch.georgia.gov/health-planning-databases
(Annual Hospital Questionaire - 2000 to 2014)
*/

CREATE TABLE hospital_compare.perinatal
SELECT b.uid, b.Medicare_Provider_No,
       c.HospitalName,
       a.Delivery_Rms,
       a.Birthing_Rms,
       a.LDR_Rms,
       a.LDRP_Rms,
       a.C_Sect,
       a.Live_Births,
       a.Total_Births,
       a.Total_Deliveries,
       a.Beds_New_Born,
       a.Beds_Intermediate,
       a.Beds_Intensive,
       a.Adms_New_Born,
       a.Adms_Intermediate,
       a.Adms_Intensive,
       a.Days_New_Born,
       a.Days_Intermediate,
       a.Days_Intensive,
       round(a.C_Sect / a.Total_Births * 100, 0) as csection_pct,
       a.Avg_Delivery_Charge,
       a.Avg_Premature_Delivery_Charge,
       d.medicare_births,
       d.early_births,
       d.footnote
FROM ahq.perinatal a
JOIN ahq.lu_id b using(uid)
JOIN hospital_compare.hospital_names c
  ON c.providerNumber = b.Medicare_Provider_No
LEFT JOIN (
  SELECT provider_id,
         score as early_births,
         sample as medicare_births,
         footnote
  FROM hospital_compare.HQI_HOSP_TimelyEffectiveCare
  WHERE measure_id = 'PC_01'
) d ON b.Medicare_Provider_No = d.provider_id
WHERE a.year = 2013
  AND a.Total_Deliveries > 0
HAVING a.C_Sect / a.Total_Births < 1
{'fraction_non_alphanumeric': 0.07667269439421338, 'fraction_numerical': 0.014828209764918625, 'mean_word_length': 2.7378378378378376, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5128954', 'n_tokens_mistral': 1115, 'n_tokens_neox': 1042, 'n_words': 195}