with CTE_C as
group by CaseID
having count(*) = sum(case when ProgramClosureDate is null then 0 else 1 end)
p.firstname + ' ' + p.lastname AS child,
FROM CaseInfo ci
JOIN CTE_C c
ON c.CaseID = ci.CaseID
JOIN CaseChild cc
ON ci.CaseID = cc.CaseID
JOIN Party p
ON cc.ChildPartyID = p.PartyID
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. Here are my guesses.
Having a magical generic “id” is clearly absurd! Surely you never use IDENTITY! The terminology is usually “case_nbr” and yet you have a “case_id”; why? This does not seem to be normalized! Names are an tribute of a kid, so they ought to be in that name; this design flaw is a version of “attribute splitting”, a flaw so well known it has a name.
Surely you never use IDENTITY! That could mean that all those “_id” things are fake pointer chains written in SQL! Horrible!
CREATE TABLE Cases
(case_nbr CHAR(10) NOT NULL PRIMARY KEY,
case_name VARCHAR(35) NOT NULL);
CREATE TABLE Children
(case_nbr CHAR(10) NOT NULL
REFERENCES Cases (case_nbr),
program_closure_date DATE, -- null means not closed
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL);
Now the query is simple
SELECT case_nbr AS closed_case_nbr
GROUP BY case_nbr
HAVING COUNT(*) = COUNT(program_closure_date);