Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    8

    Unanswered: need help finding cases with all children closed

    Hi all -
    I know the title is confusing but I need help finding the court cases where all children associated with that case have a programClosureDate.
    I can run this query:

    CaseInfo Table
    CaseID,
    CaseNumber,
    CaseName

    CaseChild Table
    CaseID, FK to CaseInfo
    ChildPartyID, FK to PartyID in Party table
    ProgramClosureDate

    Party Table
    ID,
    PartyID,
    Firstname,
    LastName




    SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate
    FROM CaseInfo ci JOIN
    CaseChild cc ON ci.CaseID = cc.CaseID JOIN
    Party p ON cc.ChildPartyID = p.PartyID

    WHERE cc.ProgramClosureDate IS NOT NULL
    ORDER BY ci.CaseName

    But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.
    Any help is greatly appreciated.
    Andy

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    with CTE_C as
    (
        select CaseID
        from CaseChild
        group by CaseID
        having count(*) = sum(case when ProgramClosureDate is null then 0 else 1 end)
    )
    
    SELECT 
        ci.CaseNumber, 
        ci.CaseName, 
        p.firstname + ' ' + p.lastname AS child, 
        cc.programClosureDate
    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
    ORDER BY 
        ci.CaseName
    Hope this helps.

  3. #3
    Join Date
    Jan 2013
    Posts
    8
    Thanks,
    This works great
    Andy

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try a test, I believe you can change the line below:

    Code:
        having count(*) = sum(case when ProgramClosureDate is null then 0 else 1 end)
    by

    Code:
        having count(*) = count(ProgramClosureDate)
    Hope this helps.

  5. #5
    Join Date
    Jan 2013
    Posts
    8
    yes, it is exactly the same result.
    thanks again
    Andy

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    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
    FROM Children
    GROUP BY case_nbr
    HAVING COUNT(*) = COUNT(program_closure_date);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •