select distinct a.asiis_pat_id_ptr, a.pat_last_name, a.pat_first_name, a.pat_birth_date, a.age, --a.pt_state_id, a.usi, a.school_name, a.school_type, a.current_grade,
       sum(a.DTaP) as "DTaP", sum(a.Polio) as ipv, sum(a.hib) as "Hib", sum(a.Hep_A) as "Hepatitis A",sum(a.Hep_B) as "Hepatitis B", --sum(a.dtp) as dtp, sum(td) as "Td", 
       sum(a.hpv) as hpv, sum(a.flu) as flu, sum(a.mmr) as mmr, sum(a.Meningococcal_B) as "Men B", sum(a.Meningococcal) as "Meningococcal",  --sum(pneu_poly) as "Pneumo Poly", 
       --sum(opv) as opv, sum(conj13) as "Pneumo Conj 13", sum(conj7) as "Pneumo Conj 7", sum(laiv) as laiv, 
       sum(a.Ratavirus) as "Rotavirus", sum(a.tdap) as "TdaP", sum(a.varicella) as "Varicella", sum(a.Zoster) as "Zoster", sum(a.COVID_19) as "COVID-19", sum(a.rsv) as rsv
from  
(
select vm.asiis_pat_id_ptr, pm.pat_first_name, pm.pat_last_name, pm.pat_birth_date, trunc((sysdate - pm.pat_birth_date)/365) Age,
       --(fm.name || ' - ' || fm.vfc_pin) as facility_name, 
       --vm.cdc_vacc_code as cvx_code,
       --vm.asiis_vacc_code,
       --vf.family_code,
       --vc.description,
       --vg.description vaccine_name,
       count(case when vf.family_code = 1 then vm.vacc_date end) DTaP,
       count(case when vf.family_code = 2 then vm.vacc_date end) Hib,
       count(case when vf.family_code = 3 then vm.vacc_date end) Polio, 
       count(case when vf.family_code in (4,12) then vm.vacc_date end) Hep_B, --combination of HepB 3 Dose and HepB 2 Dose
       count(case when vf.family_code in (5,15,16,17) then vm.vacc_date end) MMR,--combination of M,M,R and MMR codes
       count(case when vf.family_code = 6 then vm.vacc_date end) Varicella,
       count(case when vf.family_code in (7,25,26) then vm.vacc_date end) Meningococcal,  
       count(case when vf.family_code = 9 then vm.vacc_date end) Hep_A, 
       count(case when vf.family_code = 10 then vm.vacc_date end) Flu, 
       count(case when vf.family_code in (11,18) then vm.vacc_date end) Pneumococcal, --combination of Pneumo(PCV) and Pneumo(PPSV)
       count(case when vf.family_code = 13 then vm.vacc_date end) Anthrax, --Not active for ImmuCast
       count(case when vf.family_code = 14 then vm.vacc_date end) Smallpox, --Not active for ImmuCast
       count(case when vf.family_code = 20 then vm.vacc_date end) Ratavirus, 
       count(case when vf.family_code = 21 then vm.vacc_date end) HPV, 
       count(case when vf.family_code = 22 then vm.vacc_date end) Zoster, 
       count(case when vf.family_code = 23 then vm.vacc_date end) "NovelH1N1-09", --Not active for ImmuCast
       count(case when vf.family_code = 24 then vm.vacc_date end) Tdap, --Not active for ImmuCast (Included in DTaP/DT/Td/Tdap)
       count(case when vf.family_code in (25,26) then vm.vacc_date end) Meningococcal_B, --combination of Recombinant and OMV
       count(case when vf.family_code = 27 then vm.vacc_date end) BCG, 
       count(case when vf.family_code = 28 then vm.vacc_date end) Cholera, 
       count(case when vf.family_code = 29 then vm.vacc_date end) "Japanese Encephalitis", 
       count(case when vf.family_code = 30 then vm.vacc_date end) Rabies, 
       count(case when vf.family_code = 31 then vm.vacc_date end) Typhoid,
       count(case when vf.family_code = 32 then vm.vacc_date end) "Yellow Fever",
       count(case when vf.family_code = 33 then vm.vacc_date end) COVID_19,
       count(case when vf.family_code = 37 then vm.vacc_date end) rsv 
from h33_vaccination_master vm
inner join h33_patient_master pm on vm.asiis_pat_id_ptr = pm.asiis_pat_id
inner join h33_facility_master fm on vm.asiis_fac_id = fm.asiis_fac_id
left join h33_irms i on i.irms_sys_id = vm.last_irms_sys_id_to_update and vm.last_irms_sys_id_to_update is not null
left join h33_vaccine_family vf on vm.asiis_vacc_code = vf.asiis_vacc_code and vm.last_irms_fac_id_to_update is not null
left join h33_vaccine_family_code vc on vf.family_code = vc.family_code
left join h33_vacc_group_family vg on vc.family_code = vg.family_code
where fm.inactive_status is null
and vm.asiis_vacc_code is not null
and vm.deletion_date IS NULL
--and i.irms_sys_id = 1039
group by vm.asiis_pat_id_ptr, pm.pat_first_name, pm.pat_last_name, pm.pat_birth_date, trunc((sysdate - pm.pat_birth_date)/365)
       --(fm.name || ' - ' || fm.vfc_pin), 
       --vm.cdc_vacc_code,
       --vm.asiis_vacc_code,
       --vf.family_code,
       --vc.description,
       --vg.description
) a
group by a.asiis_pat_id_ptr, a.pat_last_name, a.pat_first_name, a.pat_birth_date, a.age
order by a.asiis_pat_id_ptr, a.pat_first_name, a.pat_last_name

--select * from h33_vaccine_family

--select * from h33_vaccine_family_code
/*
select distinct vf.family_code, vc.description, vm.cdc_vacc_code, vc.cdc_vaccine_group
from h33_vaccination_master vm
inner join h33_patient_master pm on vm.asiis_pat_id_ptr = pm.asiis_pat_id
inner join h33_facility_master fm on vm.asiis_fac_id = fm.asiis_fac_id
left join h33_irms i on i.irms_sys_id = vm.last_irms_sys_id_to_update and vm.last_irms_sys_id_to_update is not null
left join h33_vaccine_family vf on vm.asiis_vacc_code = vf.asiis_vacc_code and vm.last_irms_fac_id_to_update is not null
left join h33_vaccine_family_code vc on vf.family_code = vc.family_code
left join h33_vacc_group_family vg on vc.family_code = vg.family_code
where fm.inactive_status is null
and vm.asiis_vacc_code is not null
and vf.family_code = 24
*/