Having Count in DataSource

[haleb]

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

[Lech Kulikowski]

Hello,

Please check the SQL documentation in that case.

Thank you.

[haleb]

Hi Lech,

Do you have a link for reference? I have already validated that the syntax is correct by SQL standards.

[Lech Kulikowski]

Hello,

Please check
https://www.w3schools.com/sql/sql_having.asp
https://docs.microsoft.com/en-us/sql/t- ... rver-ver15

Thank you.