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-
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