Page 1 of 1

Having Count in DataSource

Posted: Mon May 18, 2020 4:29 pm
by 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

Re: Having Count in DataSource

Posted: Mon May 18, 2020 7:56 pm
by Lech Kulikowski
Hello,

Please check the SQL documentation in that case.

Thank you.

Re: Having Count in DataSource

Posted: Mon May 18, 2020 8:07 pm
by haleb
Hi Lech,

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

Re: Having Count in DataSource

Posted: Tue May 19, 2020 11:40 am
by Lech Kulikowski