SELECT DISTINCT a.W6Key KEY, ae.StartTime, ae.FinishTime, a.CommentText,NONAVAILABILITYTYPE NATypeKey, SeriesKey, NAKeyword, NVL(un.FullName, replace (a.Modifiedby, 'UTILITIES\','') )Modifiedby , a.LocationKey FROM W6ASSIGNMENTS a LEFT OUTER JOIN W6USER_SETTINGS un ON lower(Owner) = lower (a.Modifiedby) INNER JOIN W6ASSIGNMENTS_ENGINEERS ae ON a.W6Key = ae.W6Key Inner Join ( SELECT W6Key FROM W6ENGINEERS WHERE Depot IN ( SELECT W6Key from W6Depot WHERE ParentDistrict = :DistrictKey AND ActiveDepot = -1) UNION SELECT DISTINCT c.W6Key FROM W6CREW_ALLOCATIONS tpm INNER JOIN W6ENGINEERS c ON tpm.Crew = c.W6Key INNER JOIN W6ENGINEERS e ON tpm.ALLOCATEDRESOURCE = e.W6Key AND e.Depot IN ( SELECT W6Key from W6Depot WHERE ParentDistrict = :DistrictKey AND ActiveDepot = -1) WHERE tpm.FinishTime > :StartDate AND tpm.StartTime < :FinishDate UNION SELECT DISTINCT ALLOCATEDRESOURCE FROM W6CREW_ALLOCATIONS tpm INNER JOIN W6ENGINEERS c ON tpm.Crew = c.W6Key AND c.Depot IN ( SELECT W6Key from W6Depot WHERE ParentDistrict = :DistrictKey AND ActiveDepot = -1) INNER JOIN W6ENGINEERS e ON tpm.ALLOCATEDRESOURCE = e.W6Key WHERE tpm.FinishTime > :StartDate AND tpm.StartTime < :FinishDate ) e ON ae.EngineerKey = e.W6Key WHERE ae.FinishTime > :StartDate AND ae.StartTime < :FinishDate AND a.TASK IS NULL AND NONAVAILABILITYTYPE IS NOT NULL ORDER BY 1