Count of granted SPCs ranked by application date

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

carmen.calatrava
Posts: 8
Joined: Mon May 22, 2017 5:36 pm

Count of granted SPCs ranked by application date

Post by carmen.calatrava » Thu Aug 31, 2017 11:55 pm

Dear member of the forum,

I am retrieving data on SPCs (Supplementary Protection Certificates) in the Netherlands. I am comparing the results of my queries against a previous study that I found in the Internet, and unfortunately, the results are very different. Therefore, I wonder if I am missing any detail in my queries.

For instance, I am using the following query to count the number of granted SPCs ranked by application date in the Netherlands per year:

Code: Select all

SELECT COUNT(DISTINCT(spc_nr)), YEAR(spc_filing_date)
FROM tls231_inpadoc_legal_event
WHERE event_code IN ('KC1', 'SPCG') -- KC1, SPCG = Grant of a supplementary protection certificate
AND event_auth = 'NL'
GROUP BY YEAR(spc_filing_date)
ORDER BY YEAR(spc_filing_date);
However, the results are very different to the first column of the table in page 58 of the study that I found.

The second and third column of the same table correspond to the number of SPCs with a duration greater than 60 months and less than 60 months. Unlike in the study, my count of SPCs with a duration greater 60 months only delivers results for 2017:

Code: Select all

SELECT COUNT(DISTINCT(inpa.spc_nr)), YEAR(inpa.spc_filing_date)
FROM tls231_inpadoc_legal_event inpa JOIN tls201_appln a ON inpa.appln_id = a.appln_id
WHERE inpa.event_auth = 'NL'
WHERE inpa.event_code IN ('KC1', 'SPCG')
AND DATEDIFF(MONTH, a.appln_filing_date, inpa.spc_extension_date) > 300 -- 240 months of patent + 60 months of SPC 
AND YEAR(inpa.spc_extension_date) <> 9999
AND YEAR(inpa.spc_filing_date) <> 9999
GROUP BY YEAR(inpa.spc_filing_date)
ORDER BY YEAR(inpa.spc_filing_date);
I would appreciate your feedback on my queries. I am aware that I should not expect the exactly the same results as in this study, but the differences are such that I wonder if there is anything that I am not taking account.

Thank you so much in advance!
Best regards,
Carmen C


Post Reply