Left Outer Join returning unwanted data

[luvtitans50]

I have a query that I am doing a left outer join on and it returns rows with all "0" and I don't want those to show; how can I do about doing this? I tried doing something like:

a <>0 or b <> 0 or c <> 0 but that eliminates needed data?

select
clientname, c.clientid,
isnull(s.servicelinedescription,'Other') as serviceline,
'newpositions' = isnull(a.newopenings,0),
'Submittals' = submittals,
'totalheadcount' = headcount,
'unfilledpositions' = Case when a.newopenings < totalactive then 0
else a.newopenings - totalactive end,
'numerator' = ontime,
'fills' = fills,
'timetofill' = timetofill,
'newopenings' = newopenings,
'totalactive' = b.totalactive,
'futurepositions' = futurepositions,
latefilledpositions,
earlyfilled

from clients c
left join
--get orders that started this month
(
select clientid,
sum(case when o.startdate between @startdate and @enddate then originalopenings else 0 end) as newopenings,
sum(case when o.startdate > @enddate then originalopenings else 0 end) as futurepositions,
COUNT(Case when o.startdate between @startdate and @enddate then orderid end) as totalorders

from orders o
where o.statusid in (144,157) --only include open or filled orderse
and
o.startdate IS NOT NULL
group by clientid
)a on a.clientid = c.clientid

 

[Aluminum]

So you don't want client records with newopenings = 0 and futurepositions = 0 and totalorders = 0 reported?

Why not include that in a WHERE clause?

After the left join add WHERE a.newopenings <> 0 and a.futurepositions <> and a.totalorder <> 0

Not sure if you need to handle nulls?

 

[SDB15]

This looks like a job for a UNION ALL. You can have multiple where clauses and still return one table (OVERLAP may also be useful depending on the desired results.

 

[Aluminum]

Is the data returning rows were all counts are zero OR are you getting duplicates? I was thinking, you could add together all of the counts and evaluate to make sure total count is greater than zero. Then, you would be sure at least one row has a record count of one.