Code: Select all
select
wo.[EndDate],
wo.[WorkOrderID],
wo.[ID],
woa.[AssetID],
trm.*
from
[reports].[WorkOrders] wo
Left Join [reports].[InventoryTransactions] invt On wo.[ID]=invt.[WorkOrderID]
Left Join [reports].[WorkOrderAssets] woa on woa.[ID]=wo.[ID]
Join [reports].[WorkOrderForm_TrafficLightingRepairMaintenance] trm On trm.ID = wo.ID
where
wo.[DepartmentName]='TPW Traffic'
and wo.[StatusName]='Closed'
and woa.[AssetID] like 'STL%'
and ((invt.[Name] like '%LED%' OR invt.[Name] like '%led%')
and (invt.[Name] like '%Head%' OR invt.[Name] like '%head%'
OR invt.[Name] like '%LAMP%' OR invt.[Name] like '%lamp%'))
group by wo.[WorkOrderID]
having count(woa.[AssetID]) = 1