Help with Query

I have the following query

SELECT a.ID, a.Observer, a.ObservationDate, c.Name, d.Name as DeptName FROM OBSERVATION a JOIN OBSERVATIONCATEGORING b
ON a.ID = b.ObservationID JOIN CATEGORY c
ON b.CategoryID = c.ID JOIN DEPARTMENT d
ON a.DepartmentID = d.ID
WHERE  (d.Name like 'XXX Inspection Serv%')  and ObservationDate >='2014-10-03' and ObservationDate<='2014-10-08'

It produces the following output - see attached.

What I want to do is produce the output to show records that have a name of 'XXX Inspection Serv' and have a name of 'B22' both from the 'Name' column (see excel file) NOT the DeptName column. If the "ID's" are the same it is considered the same record in the application so that record should only show once if it meets this condition. So I have put in the right column how it should be counting the records - should have 6 records in total and it does not need to be in the output shown in the Excel - just need the count.

Please provide the SQL to do this.
Thanks in advance
ExcelOutput.xls
LVL 1
mathew_sAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
try this...

Might have to change to a DISTINCT COUNT though.

SELECT COUNT(O.ID) AS [Count]
FROM OBSERVATION AS O
INNER JOIN OBSERVATIONCATEGORING AS OCat
	ON O.ID = OCat.ObservationID
INNER JOIN CATEGORY AS Cat1
	ON OCat.CategoryID = Cat.ID
	AND Cat.name = 'XXX Inspection Serv'
INNER JOIN CATEGORY AS Cat2
	ON OCat.CategoryID = Cat.ID
	AND Cat.name = 'B22'
INNER JOIN DEPARTMENT AS
	ON O.DepartmentID = D.ID
WHERE D.Name like 'XXX Inspection Serv%'
	AND O.ObservationDate >='2014-10-03'
	AND O.ObservationDate<='2014-10-08' 

Open in new window

enrmmobaCommented:
WHERE ( ((d.Name like 'XXX Inspection Serv%') OR ((d.Name like '%B22%'))  AND ( ObservationDate >='2014-10-03' and ObservationDate<='2014-10-08' ))

Open in new window

Deepak ChauhanSQL Server DBACommented:
Try this as well hope you will get desired result.

select ID,Observer, ObservationDate, Name, DeptName from (
select p.*,
stuff( (select distinct ',' +pr.name from
(SELECT a.ID, a.Observer, a.ObservationDate, c.Name, d.Name as DeptName
FROM OBSERVATION a JOIN OBSERVATIONCATEGORING b
 ON a.ID = b.ObservationID JOIN CATEGORY c
 ON b.CategoryID = c.ID JOIN DEPARTMENT d
 ON a.DepartmentID = d.ID
 WHERE  (d.Name like 'XXX Inspection Serv%')  
 and ObservationDate >='2014-10-03' and ObservationDate<='2014-10-08')PR
 where p.id=pr.id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') V_Name      
FROM (SELECT a.ID, a.Observer, a.ObservationDate, c.Name, d.Name as DeptName
FROM OBSERVATION a JOIN OBSERVATIONCATEGORING b
 ON a.ID = b.ObservationID JOIN CATEGORY c
 ON b.CategoryID = c.ID JOIN DEPARTMENT d
 ON a.DepartmentID = d.ID
 WHERE  (d.Name like 'XXX Inspection Serv%')  
 and ObservationDate >='2014-10-03' and ObservationDate<='2014-10-08') p)T  
 where V_name='B22,TUV,XXX Inspection Serv'
awking00Information Technology SpecialistCommented:
With CTE as
(<yourquery>)
select count(*) from
(select id from cte where name = 'B22'
 intersect
 select id from cte where name = 'XXX Inspection Serv') x

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
mathew_s, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.