SQL Script help - Records that do not have a completed date

  • I have one table called PatentMaster (1) which links to another table called PatentAction (2). I'm trying to find any records in table 1 where in table 2 there are no CompletedDate or all records have a CompletedDate in table 2. The SQL script below returns around 6000 rows. I'm not sure how to go about creating the script to do this. Any help would be appreciated.

    For example like this data.

    Docket No Description CompletedDate

    33232-2232 Filed NULL

    Docket_No Description CompletedDate

    55859-555 Filed 02/02/2013

    55859-555 Filed 05/24/2014

    ----------------------------------------------------------------

    Ignore this data

    Docket No Description CompletedDate

    3323-442 Filed NULL

    3323-442 Filed 05/24/2014

    -----------------------------------------------------------------

    SELECT

    pm.PATENTMASTERID,

    pm.DOCKETNUMBER,

    c.DESCRIPTION,

    p.COMPLETEDDATE

    FROM dbo.PATENTMASTERS pm

    INNER JOIN CODES c

    ON pm.STATUS = c.CODEID

    LEFT JOIN PATENTACTIONS p

    ON pm.PATENTMASTERID = p.PATENTMASTERID

    WHERE c.DESCRIPTION = 'Filed'

    ORDER by pm.DOCKETNUMBER

  • Something like below I guess. I can't test it (and thus also can't review the query plan to check potential performance) because I don't have data with which to do so:

    SELECT

    pm.PATENTMASTERID,

    pm.DOCKETNUMBER,

    c.DESCRIPTION,

    pa.COMPLETEDDATE

    FROM dbo.PATENTMASTERS pm

    INNER JOIN CODES c

    ON pm.STATUS = c.CODEID

    INNER JOIN (

    SELECT PATENTMASTERID, MAX(CompletedDate) AS CompletedDate --or MIN(CompletedDate) AS CompletedDate

    FROM PATENTACTIONS

    GROUP BY PATENTMASTERID

    HAVING COUNT(*) IN (

    SUM(CASE WHEN CompletedDate IS NULL THEN 1 ELSE 0 END),

    SUM(CASE WHEN CompletedDate IS NOT NULL THEN 1 ELSE 0 END)

    )

    ) AS pa

    ON pm.PATENTMASTERID = pa.PATENTMASTERID

    WHERE c.DESCRIPTION = 'Filed'

    ORDER by pm.DOCKETNUMBER

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • You're brilliant. That worked perfectly!!! Thank you very much!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply