March 25, 2015 at 1:46 pm
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
March 25, 2015 at 1:59 pm
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.
March 25, 2015 at 3:10 pm
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