query pulling null records
Good afternoon,
i tried using having and this query still pulls records that have all nulls and not just records that have person_id in EPPSGlobalProd.dbo.person and in one of the 7 tables in the CASE statement. Does anyone know what I am doing wrong?
SELECTDISTINCT p.person_id,
p.last_name,
p.first_name,
o.name as office_name, g.BU,
case
when
count(c.clean_cut_experience_id)> 0
then'X'
end clean_cut_experience,
case
whencount(r.cri_experience_id)> 0
then'X'
end cri_experience,
case
when
count(d. desorption_tech_experience_id)> 0
then'X'
end desorption_tech_experience,
case
when
count(w.dwm_wellsite_experience_id)>0
then'X'
end dwm_wellsite_experience,
case
whencount( f.fluids_experience_id)> 0
then'X'
end fluids_experience,
case
whencount(pressure_control_equipment_experience_id)> 0
then'X'
end pressure_control_equipment_experience,
case
whencount(rdf_experience_id)> 0
then'X'
end rdf_experience,
case
whencount(speedwell_experience_id)> 0
then'X'
end speedwell_experience,
case
whencount(completion_procedures_experience_id)> 0
then'X'
end completion_procedures_experience
FROM global_business_unit g
join LogITProd.dbo.office o on g.office_name = o.name
join LogITProd.dbo.security_user_office_assignment u on u.office_id = o.office_id
join EPPSGlobalProd.dbo.person p on p.ad_guid = u.ad_guid
leftouterJOIN clean_cut_experience c ON p.person_id = c.person_id
leftouterJOIN cri_experience r ON p.person_id = r.person_id
leftouterJOIN desorption_tech_experience d ON p.person_id = d.person_id
leftouterJOIN dwm_wellsite_experience w ON p.person_id = w.person_id
leftouterJOIN fluids_experience f ON p.person_id = f.person_id
leftouterJOIN pressure_control_equipment_experience s ON p.person_id = s.person_id
leftouterJOIN rdf_experience x ON p.person_id = x.person_id
leftouterJOIN speedwell_experience n ON p.person_id = n.person_id
leftouterJOIN completion_procedures_experience e ON p.person_id = e.person_id
--where o.name ='Aberdeen'
groupby p.person_id, p.last_name, p.first_name,o.name, g.BU
HAVINGCount(c.clean_cut_experience_id)
+ Count(r.cri_experience_id)
+ Count(d.desorption_tech_experience_id)
+ Count(w.dwm_wellsite_experience_id)
+ Count(f.fluids_experience_id)
+ Count(pressure_control_equipment_experience_id)
+ Count(rdf_experience_id)
+ Count(speedwell_experience_id)
+ Count(e.completion_procedures_experience_id)
IS NOT NULL
orderby o.name
|
