I had this question after viewing How can I add this SQL to the existing SQL I have?
Paul helped me with the the solution in the link above and it works but I just realized I need to have another clause. I tried it in the "where" clause but that won't work.
1. I need to have this
LoadTypeId not in (2, 3,6,10)
2. We actually have a "view" to read the enums so instead of #1, I want to use the view
I have it to here but how can I add another having clause to it?
This is SQL 2014
, count(CASE WHEN (lv.Name = 'Settled') THEN l.Loadid END) AS SettledLoads
,count(CASE WHEN ( lv.Name = 'Waived') THEN l.Loadid END) AS WavedLoads
left join dbo.LoadStatusER as V
on V.enumId = loadStatus
left join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId ---*** I added this but now how do I exclude the values?
where shipdate >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
--and LoadTypeId not in (2, 3,6,10) --*** this won't work
group by CommSalesOrder_CommSalesOrderId
having sum(case when v.enumId is not null then 0 else 1 end) = 0
) AS lgroup
INNER JOIN CommSalesOrders AS c ON c.CommSalesOrderId = lgroup.CommSalesOrder_CommSalesOrderId
inner join loads l ON c.CommSalesOrderId = l.CommSalesOrder_CommSalesOrderId
inner join accounts a on a.AccountId = c.Seller_AccountId
--inner join Addresses ad on ad.AccountId = a.AccountId
inner join [dbo].[LoadStatusER] lv on l.LoadStatus = lv.enumId