need to select on COUNT (WHERE / HAVING ??)

[Windy77]

I am trying to identify from a table of clients, only those who have handled a particular type of case (kf1.WorkSource2 = 4). This is in addition to more standard cases (there are some who never have kf1.WorkSource2 = 4 so the COUNT would be 0), so I want to ignore those where the COUNT is 0.

The script below produces a list of ALL including the COUNT = 0. I've tried the GROUP by / Having route (ending the sub-query further after the HAVING) but this doesn't work.

I can export the result to Excel & use a sort to identify the 0 lines for deletion, but I'd like to be able to automate and schedule this so need to resolve the SQL issue.

SELECT ts.client, ts.clientPostCode,
(SELECT COUNT(kf1.FileId)
FROM Reports1.dbo.KeyFields kf1
WHERE kf1.WorkSource2 = 4
AND ts.clientId = kf1.client2) as Private_Cases

-- GROUP BY ts.clientId
-- HAVING COUNT(kf1.FileId) > 0) as Private_Cases

FROM v2data.dbo.tclient ts
WHERE clientStatus = 'ACTIVE'
ORDER BY ts.clientName

Help would be appreciated.

Paul

 

[RiazKhanmohamed]

you missed the closing bracket, but try this:

SELECT distinct ts.client, ts.clientPostCode
FROM v2data.dbo.tclient ts
inner join Reports1.dbo.keyfields kfl
on ts.clientId = kf1.client2
WHERE ts.clientStatus = 'ACTIVE'
and kf1.WorkSource2 = 4
ORDER BY ts.clientName

you shouldn't get 0 counts. hopefully this helps

 

[Windy77]

Thanks for response.

Not sure which bracket I've missed, but I should have clarified that I've already listed them exactly as you have shown & it works perfectly (except the 'Order By' column name needs to be in the Select list - as I'm sure you'd have seen from syntax checker if you were working with tables rather than providing suggestions !).

As I said that solution you wrote works, but I need this to be more flexible for future use. Right now I just need to report those with private cases, but I know someone will decide some arbitary figure (to represent "not many" private cases) & I'll have to code it to be COUNT > 10 for example.

It may not be possible this way. I may be able to add some sort of filter to the DTS to drop it into Excel but I was hoping to be able to set the value up as a parameter to pass into the SP.

Any more ideas ?

 

[RiazKhanmohamed]

i think you may need to create a view that displays a clientid, caseid and count of cases. then you can link to this as above and have a where clause on the views count field.

 

[Windy77]

that sounds like a plan. I'll give it a try tmoorrow & get back to you.

Thanks.
Paul