SELECT
    dbo.tPerson.ID AS PersonID, dbo.tPerson.Person,
    dbo.tServiceID.ID, dbo.tServiceID.ServiceID,
    dbo.tLocation.ID, dbo.tLocation.Description,
    dbo.tDepartment.ID, dbo.tDepartment.Description,
    dbo.tCustomer.ShipTo, dbo.tCustomer.FullName
FROM
    dbo.tPerson
    LEFT OUTER JOIN dbo.tGroup
	    INNER JOIN dbo.tCentre
	    INNER JOIN dbo.tServiceID
		    LEFT OUTER JOIN dbo.tLocation
			ON dbo.tLocation.ID = dbo.tServiceID.LocationID
		ON dbo.tCentre.ID = dbo.tServiceID.CustomerCentreID
	    INNER JOIN dbo.tDepartment
		ON dbo.tCentre.DepartmentID = dbo.tDepartment.ID
        ON dbo.tGroup.ID = dbo.tDepartment.GroupID
    INNER JOIN dbo.tCustomer
        ON dbo.tGroup.CustomerID = dbo.tCustomer.ID
    ON dbo.tPerson.ID = dbo.tServiceID.PersonID
WHERE
    (dbo.tPerson.Active = 1) AND (dbo.tServiceID.Active = 1)
  AND (NOT (dbo.tServiceID.ServiceID LIKE 'kamco%'))
  AND (NOT (dbo.tServiceID.ServiceID LIKE 'annual%'))
ORDER BY dbo.tPerson.Person
