Anyone know of the best way to find a list of keywords assigned to performances? I'm a beginner Tess admin with very basic SQL skills and have not had good luck finding a way to query this information easily. I tried to use list manager to "cheat", but we are cleaning up all keywords that may cover all of our performances and this exceeded what the system would output. Looking to see which are actually inactive/unused and to test what happens if I delete an assigned keyword on an old performance, assuming no one is using that keyword to pull lists, etc. anymore.
Carlie,
What you are looking for here is the TX_INV_TKW table. Because keywords can be placed on any of four levels (Title, Production, Production Season, Performance), the reference number on that table is an inventory number (inv_no) and matches the corresponding level. So finding the different keywords could depend upon at what level your organization has placed them. Additionally, my own two cents, I tend to avoid deleting things if possible. Since TR_TKW does not have an "inactive" column (why not? I do not know, it seems like it should...), what we have done is to create a control group for archival. I have simply placed all of our old keywords into the archive control group, so that way no one else can see them. Lastly, Tessitura usually (rightly) prevents you from deleting something like that if it is attached to something else, so you might not even be able to do that in the first place.
As for the query itself, here is some code that will return all keywords attached to performances after a given minimum performance date. This might look a little scary, but is actually a pretty straight forward pull of the data. Any keyword not returned by this query has not been attached to a performance in the desired time frame. If you are just looking for the distinct keywords themselves, you can either modify the SQL here to get you that, or you could simply paste the output in Excel and hit "remove duplicates" on that column.
USE impresario DECLARE @min_perf_dt DATETIME = '2009-01-01 00:00:00.000' ; --Terminating for CTE usage WITH cte_perfs AS ( SELECT p.perf_code, i.description AS perf_name, p.perf_dt, p.perf_no, p.prod_season_no, ps.prod_no, pr.title_no FROM T_PERF p JOIN T_PROD_SEASON ps ON p.prod_season_no = ps.prod_season_no JOIN T_PRODUCTION pr ON ps.prod_no = pr.prod_no JOIN T_INVENTORY i ON p.perf_no = i.inv_no WHERE p.perf_dt >= @min_perf_dt ) SELECT c1.perf_no, c1.perf_code, c1.perf_name, c1.perf_dt, x1.tkw, t1.description AS keyword_desc FROM TX_INV_TKW x1 JOIN TR_TKW t1 ON x1.tkw = t1.id JOIN cte_perfs c1 ON x1.inv_no = c1.perf_no UNION SELECT c2.perf_no, c2.perf_code, c2.perf_name, c2.perf_dt, x2.tkw, t2.description AS keyword_desc FROM TX_INV_TKW x2 JOIN TR_TKW t2 ON x2.tkw = t2.id JOIN cte_perfs c2 ON x2.inv_no = c2.prod_season_no UNION SELECT c3.perf_no, c3.perf_code, c3.perf_name, c3.perf_dt, x3.tkw, t3.description AS keyword_desc FROM TX_INV_TKW x3 JOIN TR_TKW t3 ON x3.tkw = t3.id JOIN cte_perfs c3 ON x3.inv_no = c3.prod_no UNION SELECT c4.perf_no, c4.perf_code, c4.perf_name, c4.perf_dt, x4.tkw, t4.description AS keyword_desc FROM TX_INV_TKW x4 JOIN TR_TKW t4 ON x4.tkw = t4.id JOIN cte_perfs c4 ON x4.inv_no = c4.title_no ORDER BY perf_dt, perf_no
Hope that helps, and best of luck in your keyword cleaning. I definitely remember doing that about 5 years ago, and it is certainly nice to have things a little neater and more organized.
John A. Moskal II
Appreciate the SQL, that is exactly what I'm looking for. I will take your advice into consideration as we determine next steps in how to handle these. Thank you!