SELECT
imm_nor.ufo_num as [Order Number]
,person.last_name as [Last Name]
,person.first_name as [First Name]
,convert ( VARCHAR(10),convert (date, person.date_of_birth),101)as [DOB]
,DATEDIFF(HOUR,person.date_of_birth,GETDATE())/8766 as [age]
,convert (VARCHAR(10),imm_order_vaccines.create_timestamp,101) as [Order Date]
,imm_order_vaccines.administer_year as [Admin Year]
,imm_order_vaccines.administer_month as [Admin Month]
,imm_order_vaccines.administer_day as [Admin Day]
,imm_order_vaccines.vaccine_desc as [Vaccine]
,imm_order_vaccines.lot_num as [Lot Num]
,convert (VARCHAR(10),imm_order_vaccines.expiration_date,101) as [Exp Date]
,imm_order_vaccines.manufacturer_name as [Manufacturer]
,imm_order_vaccines.units as [Units]
,imm_order_vaccines.dose as [Dose]
,imm_order_vaccines.route as [Route]
,imm_order_vaccines.site as [Site]
,imm_order_vaccines.vaccine_status as [Status]
,user_mstr.login_id as [Administer By]
,location_mstr.location_name as [Location]
,imm_order_vaccines.vaccine_comment
FROM
imm_order_vaccines
INNER JOIN imm_nor ON imm_order_vaccines.order_num = imm_nor.order_num
INNER JOIN person ON imm_nor.person_id = person.person_id
LEFT OUTER JOIN location_mstr on imm_nor.location_id = location_mstr.location_id
LEFT OUTER JOIN user_mstr on imm_order_vaccines.administer_by = user_mstr.user_id
WHERE
(location_mstr.location_name = 'Mommy And Me 4th St Peds' and imm_nor.ordering_provider is not null and (imm_order_vaccines.vfc_code <> 'V01' or imm_order_vaccines.vfc_code is null)) and ((DATEDIFF(HOUR,person.date_of_birth,GETDATE())/8766) <= '18')
or (location_mstr.location_name = 'Mommy And Me 4th St OBGYN' and imm_nor.ordering_provider is not null and (imm_order_vaccines.vfc_code <> 'V01' or imm_order_vaccines.vfc_code is null)) and ((DATEDIFF(HOUR,person.date_of_birth,GETDATE())/8766) <= '18')
or (location_mstr.location_name = 'Mommy And Me 4th St Family' and imm_nor.ordering_provider is not null and (imm_order_vaccines.vfc_code <> 'V01' or imm_order_vaccines.vfc_code is null)) and ((DATEDIFF(HOUR,person.date_of_birth,GETDATE())/8766) <= '18')
ORDER BY
imm_order_vaccines.administer_year desc
,imm_order_vaccines.administer_month desc
,imm_order_vaccines.administer_day desc
