Having Count in DataSource
Posted: Mon May 18, 2020 4:29 pm
We are creating a report that is looking for a 1:1 ration between two record types. Below is the datasource I am trying to create. The issue I am running into is; with the group by and having count clause the columns do not retrieve. However if I remove those two lines the columns retrieve as expected. Is there another way to write this so that I only get records with a 1:1 ratio?
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