I'm embarrassed to say I've completely stumped myself with this. I assumed it would be simple for me, or that I'd be able to use some views around Content elements to work it out, but neither is the case.
In the way that, with Content elements, you can attach them to any level of the production element tree and they will flow downwards, but also be overruled by more specific rules, I'd built a configuration table like this:
title_no
prod_no
prod_season_no
perf_no
[configuration information]
I then expected to produce a view that just had
that I could match to transactions. The idea is that if I have
title_no = 1, config = 'A'
and
perf_no = 5, config = 'B'
And title_no 1 filters down to perf_nos 5-8 I would get
I'm seeing a couple of views that pull together the Content elements using unions, but no plan for choosing which configuration to use, which I guess is done in the application.
Gawain Lavers I am unsure if this will be helpful but I use a view that pulls a listing of customer performances that have specific keywords attached that determines whether is a particular performance valid for use in the view. The keywords can be attached at either of the 4 Production Elements levels with the Performance level having the highest priority coming back up to the Titles level having the lowest priority. The view makes use of multiple ISNULL() functions returning only the most specific keyword and may be a bit crude in hindsight but does what I need it to do with minimal execution time......Happy to share this view with you if relevant but just wanted to share what worked for me. Thanks!
Thanks Andrew: I'd love to at least scan it.
Gawain Lavers Apologies for the late reply. Here's a trimmed down version of what I use:
Select a.customer_no, a.perf_no, a.perf_dt, a.season, Perf_Venue_ID = a.theater_no , Perf_Venue = LTrim(RTrim(a.theater_desc)), Perf_Status = Case When a.perf_dt >= GetDate() Then 'Future' Else 'Past' End, Perf_Day = Convert(DateTime, Convert(VarChar, a.perf_dt, 112)), Perf_Inclusion = Case When IsNull(IsNull(IsNull(IsNull(i.tkw, j.tkw), k.tkw), l.tkw), 0) = 335 Then 'Y' When IsNull(IsNull(IsNull(IsNull(i.tkw, j.tkw), k.tkw), l.tkw), 0) = 336 Then 'N' Else 'N' End From VS_ELEMENTS_TICKET_HISTORY a With (NoLock) Left Join (Select inv_no, tkw = Max(tkw) From TX_INV_TKW With (NoLock) Where tkw In (335, 336) Group By inv_no) i On i.inv_no = a.perf_no -- Performance level in- / exclusion keyword Left Join (Select inv_no, tkw = Max(tkw) From TX_INV_TKW With (NoLock) Where tkw In (335, 336) Group By inv_no) j On j.inv_no = a.prod_season_no -- Production season level in- / exclusion keyword Left Join (Select inv_no, tkw = Max(tkw) From TX_INV_TKW With (NoLock) Where tkw In (335, 336) Group By inv_no) k On k.inv_no = a.prod_no -- Production level in- / exclusion keyword Left Join (Select inv_no, tkw = Max(tkw) From TX_INV_TKW With (NoLock) Where tkw In (335, 336) Group By inv_no) l On l.inv_no = a.title_no -- Title level in- / exclusion keyword Where (a.season In (Select id From VRS_SEASON With (NoLock)) Or IsNull(a.season, 0) = 0) And a.perf_dt >= GetDate() And a.perf_no > 0 Group By a.customer_no, a.perf_no, a.perf_dt, a.theater_no, a.theater_desc, a.season, IsNull(IsNull(IsNull(IsNull(i.tkw, j.tkw), k.tkw), l.tkw), 0)
As mentioned, a little crude but does the job I need it to do with minimal time and performance impact. Hopefully this helps in some way. Thanks!