I have a query I'm trying to design but not sure where to procede on 1 field
The query links 2 tables a master and a detail table
I need to write a query that accumulates the number of times a field in the detail table is > 0 for each record in the master table
For example a master customer ID of 23 has 10 Detail Records I need to count how many of those detail records where field d is > 0
In access it's simple
Select masterid, Sum(IIF(Fieldd > 0,1,0))
From mastertable inner join detailtable on mastertable.idfield = detailtable.idfield
Where status = "1"
Group By mastered
Having Sum(IIF(Fieldd > 9,1,0)) > 1
.
Just not sure what to use in SQL for IIF
Also this is from a vendor's database so I can't add functions or stored procedures.
It has to be done in TSQL