p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Expert SQL Server 2005 Integration Services ISBN: 978-0-470-13411-5 (http://p2p.wrox.com/forumdisplay.php?f=333)
-   -   retrieving all records (http://p2p.wrox.com/showthread.php?t=73467)

jcarswelljr March 20th, 2009 03:05 PM

retrieving all records
i want to retrieve all records regardless if they were associated with an office or had person_id in the other 7 tables what do i need to add to this query to achieve this? presently the query retrieves 1200 records that have person_id in LogITProd.dbo.office, EPPSGlobalProd.dbo.person and any table in the case statement. there are 31000 records altogether.
thank you for all your help!

SELECT DISTINCT p.person_id,



o.name as office_name, g.BU,
count(c.clean_cut_experience_id) > 0
then 'X'
end clean_cut_experience,
when count(r.cri_experience_id) > 0
then 'X'
end cri_experience,
count(d. desorption_tech_experience_id) > 0
then 'X'
end desorption_tech_experience,
count(w.dwm_wellsite_experience_id) >0
then 'X'
end dwm_wellsite_experience,
when count( f.fluids_experience_id) > 0
then 'X'
end fluids_experience,
when count(pressure_control_equipment_experience_id) > 0
then 'X'
end pressure_control_equipment_experience,
when count(rdf_experience_id) > 0
then 'X'
end rdf_experience,

when count(speedwell_experience_id) > 0
then 'X'
end speedwell_experience,

when count(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
left outer JOIN clean_cut_experience c ON p.person_id = c.person_id

left outer JOIN cri_experience r ON p.person_id = r.person_id

left outer JOIN desorption_tech_experience d ON p.person_id = d.person_id

left outer JOIN dwm_wellsite_experience w ON p.person_id = w.person_id

left outer JOIN fluids_experience f ON p.person_id = f.person_id

left outer JOIN pressure_control_equipment_experience s ON p.person_id = s.person_id

left outer JOIN rdf_experience x ON p.person_id = x.person_id

left outer JOIN speedwell_experience n ON p.person_id = n.person_id

left outer JOIN completion_procedures_experience e ON p.person_id = e.person_id
--where o.name ='Aberdeen'
group by p.person_id, p.last_name, p.first_name,o.name, g.BU

HAVING Count(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)
order by o.name

All times are GMT -4. The time now is 04:08 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.