I am looking for data including this field to each patent

[itsik]

Hi,
I need your help, because I can’t find this data
I am looking for data including this field to each patent:
• application date
• patent granted
• amount of citation (for this patent till X DATE)
• Is the granted patent gave for all rows or just partly (for some rows)

The search should be by company, for example if I search "TEVA" company I should get the all patents list for the company in rows, and by columns the fields I had marked above.
I really appreciate your help, I need it for my study work
*you could see the excel down
I need to check it for each company in the S&P 500 (USA) and S&P 350 (in Europe)
thx
Itsik

[nico.rasters]

Do you have access to a local installation of PATSTAT (if so, which version?) or PATSTAT Online?

The following query would give you a list of names that have "TEVA" in them.
SELECT * FROM `tls206_person` WHERE `person_name` REGEXP "[[:<:]]TEVA[[:>:]]";
Alternatively, you can use the tls208 table.
SELECT * FROM `tls208_doc_std_nms` WHERE `doc_std_name` LIKE "Teva Pha%";
I used a different query as I am assuming that you are looking for TEVA Pharmaceuticals.

Both queries will return a list of names. You will then have to pick out the names that are good.
For example, you will find "TEVA PHARMACEUTICALS USA CORP". No doubt a subsidiary. It's up to you to decide if you only want the headquarters in Israel or the foreign subsidiaries as well.

Once you have a list of "good" names, you can find their patents by using the tables tls206, tls207, and tls201.

Anyway, your post is already quite old so I don't know if you're still working on this. If you are, I can expand my answer.