DISTINCT within Datasource
Posted: Thu Apr 09, 2020 7:45 pm
I have a report where I want to pull the first asset record that is attached to a work order only. My datasource consists of three different joins, so I am trying to avoid using a sub select. I have tried using MIN and DISTINCT within the initial select statement but neither seem to work. Is there another method to achieve this?
Below is our datasource. The field we want to return the first match on is woa.AssetID
Below is our datasource. The field we want to return the first match on is woa.AssetID
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 ((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%'))
and (cast(wo.[EndDate] as date) between @from and @to)