We want to do a bit of an audit of the keywords we have applied to our productions & production seasons. We basically want to be able to generate a list of productions that shows all the keywords that have been applied to each production. We can't use Analytics (as we don't have all the categories that we need in Analytics) and can't think of a report that would do what we need. I was wandering if anyone else has had to do something similar and would be happy to share any advice please. Thanks!
If you have someone with SQL skills on staff they should be able to put together a query for you!
Here is the SQL query I use to proof MOS and Keyword setup. I put it all within /* */ so that it is not active. You would just need to add in the Production part. Any performance that does not have a keyword will show a null for that column (see the picture at the bottom)./*
/***************
Proofing Modes of Sales set for performances within a production season and performance date range.
Neil Cole
MN Zoo
3/18/2021
--Added KEYWORD tables to proof keyword setup.
****************/
DECLARE @Prod_Season_no INT = 34632
--34619 = Zoo General Admission FY21
DECLARE @Begin_Perf_dt_1 DATETIME = '2021-05-03'
DECLARE @End_Perf_dt_2 DATETIME = '2021-05-09'
;
select
i.description [Performance]
, convert(date,p.perf_dt) [perf_dt]
, pmos.perf_no
, m.description [MOS]
, pmos.start_dt
, pmos.end_dt
--, txkw.tkw
, tkw.description as [Keyword]
from [dbo].[TX_PERF_PKG_MOS] PMOS
join TR_MOS m on m.id = pmos.MOS
join T_PERF P on p.perf_no = pmos.perf_no
join T_INVENTORY I on i.inv_no = pmos.perf_no
left join [dbo].[TX_INV_TKW] TXKW on TXKW.inv_no = P.perf_no and txkw.inv_no = I.inv_no
left join [dbo].[TR_TKW] TKW on TKW.id = TXKW.tkw
where PMOS.perf_no in
(select p.perf_no from T_PERF P where P.prod_season_no = @Prod_Season_no)
AND convert(date,p.perf_dt) between @Begin_Perf_dt_1 and @End_Perf_dt_2
*/
Thanks! That's really useful