Number of claims coverage in patstat - 2015b update

This is an update to a previous post issued in 2012 (see here)

	EP	US
1975		0,981
1976		0,9856
1977		0,9962
1978	1	0,9969
1979	0,9995	0,997
1980	0,9224	0,9978
1981	0,885	0,997
1982	0,8428	0,9965
1983	0,8436	0,9956
1984	0,8116	0,9959
1985	0,7952	0,9959
1986	0,7545	0,9937
1987	0,7094	0,9941
1988	0,7166	0,992
1989	0,7169	0,992
1990	0,7006	0,9942
1991	0,6621	0,9939
1992	0,6767	0,9947
1993	0,733	0,9935
1994	0,7644	0,9934
1995	0,7275	0,9948
1996	0,7342	0,9956
1997	0,6783	0,9955
1998	0,6522	0,9954
1999	0,6209	0,9964
2000	0,5486	0,9956
2001	0,5827	0,7706
2002	0,5867	0,5061
2003	0,5828	0,4662
2004	0,5562	0,4202
2005	0,5646	0,3626
2006	0,5732	0,4103
2007	0,5609	0,3863
2008	0,55	0,3725
2009	0,5262	0,3743
2010	0,5404	0,4295
2011	0,583	0,4437
2012	0,5606	0,4657
2013	0,5594	0,4803
2014	0,5323	0,4788
2015	0,5023	0,4683

this is the sql query to get all coverage by publication authority
(this version has @ line 18 a filter for EP and US: you can remove it if you like to double check whether other patent offices data are loaded)

Select
  t11.PUBLN_AUTH,
  Year(t11.PUBLN_DATE) As pubyear,
  ifnull(a.n_pat_c,0)/Count(Distinct t11.APPLN_ID) As claim_perc
From
  patstat.tls211_pat_publn t11 left join
(Select
  t11.PUBLN_AUTH,
  Year(t11.PUBLN_DATE) As pubyear,
  Count(Distinct t11.APPLN_ID) As n_pat_c
From
  patstat.tls211_pat_publn t11
Where
  t11.PUBLN_CLAIMS > 0
Group By
  t11.PUBLN_AUTH, Year(t11.PUBLN_DATE)) as a
on a.publn_auth=t11.PUBLN_AUTH and a.pubyear=Year(t11.PUBLN_DATE)
where t11.PUBLN_AUTH in ("EP","US")
Group By
  t11.PUBLN_AUTH, Year(t11.PUBLN_DATE)