[SQL] View to distill out the most specific rule for a configuration table using production elements

Gawain Lavers $organization over 5 years ago

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

perf_no

[configuration information]

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

perf_no	configuration
5	B
6	A
7	A
8	A

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.

Andrew Chew $organization over 5 years ago in reply to Gawain Lavers

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!