Searching in PATSTAT by Priority Data

[knikonor]

Hello colleagues!

I have a question regarding searching priority data (especially dates) in PATSTAT.
I am frustrated in what table this data is. It is marked as field 30 in WO* form like e.g. in:

https://worldwide.espacenet.com/publicationDetails/originalDocument?FT=D&date=20100910&DB=EPODOC&locale=ru_ru&CC=WO&NR=2010101694A1&KC=A1&ND=4

I'd like to count this data by year across all patstat database. How is it possible?

Thanks in advance,
Kirill.

[Geert Boedt]

Hello Kirill,

applications are linked to their "Paris convention priority applications" via the TLS204_APPLN_PRIOR table. So if you want to retrieve all priority applications from a specific application, the following query would do the job; I used the same application as what you used in your example, but you can adapt the criteria in the WHERE clause to your own specifications. In case you would like to use publication data to start of with, then you would need to create an extra JOIN with the TLS11_PAT_PUBLN table

Code: Select all

SELECT tls201_appln.appln_id 
	  ,tls201_appln.appln_nr_original
      ,tls201_appln.appln_auth 
      ,tls201_appln.appln_nr 
      ,tls201_appln.appln_kind 
      ,tls201_appln.appln_filing_date 
      ,tls204_appln_prior.prior_appln_seq_nr
      ,priority.appln_id 
      ,priority.appln_auth 
      ,priority.appln_nr 
      ,priority.appln_kind 
      ,priority.appln_filing_date 
FROM tls201_appln join tls204_appln_prior on tls201_appln.appln_id = tls204_appln_prior.appln_id
	     join tls201_appln as priority on tls204_appln_prior.prior_appln_id = priority.appln_id
where tls201_appln.appln_nr_original = 'US2010/023286'
order by prior_appln_seq_nr

[knikonor]

Hello Geert!

Thank you very much! It works fine for seraching data from one patent. But what I need is to count the amount of patents per year in classification tree by B82Y class number.

To solve that first I use the following script to get all B82Y applications per year:

use patstatapril2013
SELECT distinct year(tls201_appln.appln_filing_date) as Year, count(*) as Applns
FROM tls201_appln

LEFT OUTER JOIN tls209_appln_ipc ON tls201_appln.appln_id = tls209_appln_ipc.appln_id
LEFT OUTER JOIN tls207_pers_appln ON tls201_appln.appln_id = tls207_pers_appln.appln_id
LEFT OUTER JOIN tls224_appln_cpc on tls201_appln.appln_id = tls224_appln_cpc.appln_id
LEFT OUTER JOIN tls211_pat_publn on tls201_appln.appln_id = tls211_pat_publn.appln_id

where tls224_appln_cpc.cpc_class_symbol like 'B82Y%'
and tls201_appln.appln_id <= 900000000
or tls209_appln_ipc.ipc_class_symbol like 'B82Y%'

group by year(tls201_appln.appln_filing_date)
order by year(tls201_appln.appln_filing_date) asc

AIl riqht with this? I have strange result:

Year Applns
1898 1
1928 10
1930 30
1931 20
1932 1
1934 1
1935 13
1936 6
1937 1
1938 22
1939 10
1942 15
1943 72
1944 6
1945 1
1946 10
1947 22
1948 13
1949 67
1950 27
1951 63
1952 47
1953 74
1954 150
1955 227
1956 229
1957 132
1958 190
1959 217
1960 481
1961 733
1962 500
1963 967
1964 1606
1965 841
1966 973
1967 1852
1968 1160
1969 1625
1970 1684
1971 2212
1972 1996
1973 3731
1974 1823
1975 3068
1976 4011
1977 5000
1978 5616
1979 9751
1980 7395
1981 7861
1982 10177
1983 13555
1984 24076
1985 33023
1986 41368
1987 67716
1988 74235
1989 87716
1990 126465
1991 139891
1992 154554
1993 152195
1994 185390
1995 212421
1996 186256
1997 255122
1998 251305
1999 308912
2000 422163
2001 667267
2002 698462
2003 817113
2004 701235
2005 603248
2006 479835
2007 397272
2008 365427
2009 324376
2010 324610
2011 253825
2012 76308
9999 503

That should not be so much. How can I distinct correct results? I need all applications by B82Y counted per year.

Thanks a lot in advance,
Kirill.