Puzzle: complicated sums in T_KEYWORD List Criteria

Since the List Manager is just writing the SSMS query for us using secured views instead of the normal tables, do we know if the Show Query part of List Manager allows you to create either a tempory table, table variable, or CTE(s)? If so, one could use that to group the data and then you finally take the max (or your idea of the sum of the max). Otherwise, maybe an imbedded derived table? I bet if you were in SSMS you could figure it out. I wonder what the Show Query will allow us to do (versus what we can do in SSMS).

I don't know if this will help spark an idea for you or not, but this is the structure of a gift certificate data query shared by Ronald Radford on the Tessitura forum. I went through it and commented it so I could easily understand the structure of the query (figure out the why). I like how Ronald used a table variable to aggregate and only take the max sequence number to compute the current balance of each gift certificate.

***Ronald Radford's Query to compute Gift Certificate Purchase/Balances - Structure:***

1) Creates a Table Variable - @tblGC.

2) Inserts data into the table variable.

2a) Data from T_PAYMENT ij T_GC on p=g pmt_no ij T_TRANSACTION on p=t trans_no.

2b) SeqNo is in the Table Variable using ROW_NUMBER() partition by....

3) Then selects data from a Derived Table F which gets its data from an imbedded Derived Table D.

4) The imbedded Derived Table D aggregates the data from the Table Variable - @tblGC.

4a) The concept of using an imbedded Derived Table is why it only selects the MAX(SeqNo) as MaxSeq data is from the Table Variable.

If memory serves you can neither use variables nor CTEs in List Manager's query window.  The goal of this, though, is to provide list criteria for our regular users.

Msg 4109, Level 15, State 1, Line 206
Windowed functions cannot be used in the context of another windowed function or aggregate.

Bah, I thought I had something there...

Following, I'm curious what you come up with for this. It sounds like something where creating a View might be best?

I should perhaps define my problem a little better.  I'm creating a new set of criteria for list manager, where I want to allow users to identify customers based on, among other things, the keywords attached to performances they have tickets to.  So I might create a view like this (again very simple, obviously keywords can be attached to different levels of the production element tree, etc.):

CREATE VIEW SLI_TKW_TEST
AS
select
	o.customer_no,
	o.order_no,
	sli.sli_no,
	sli.perf_no,
	sli.paid_amt,
	kw.tkw
from T_SUB_LINEITEM as sli
	inner join T_ORDER as o on o.order_no = sli.order_no
	inner join TX_INV_TKW as kw on kw.inv_no = sli.perf_no
where
	sli.sli_status = 12
;

Now, if I want to create a list criteria to find people who have a ticket to show with a certain keyword attached, I can create a criteria in T_KEYWORD that looks like this:

description = "Performance Keyword"
detail_col = '!.tkw'
ref_tbl = 'TR_TKW'
ref_IDCol = 'id'
ref_DescCol = 'description'

And if I want to be able to say something like "I want everyone who has a tickets to two or more performances", I could use a criteria like this:

description = "Total Unique Performances"
detail_col = 'COUNT(distinct !.perf_no)'
ref_tbl = null
ref_IDCol = null
ref_DescCol = null

But if I want to say, "I want everyone who has spent over $50 on a ticket" I can do this:

description = "Total Paid Amount"
detail_col = '!.paid_amt'
ref_tbl = null
ref_IDCol = null
ref_DescCol = null

But if I say "I want everyone who ahs spent over $100 on tickets to a performance" this won't work:

description = "Total Paid Amount"
detail_col = 'SUM(!.paid_amt)'
ref_tbl = null
ref_IDCol = null
ref_DescCol = null

because that sum is going to be multiplied by the number of keywords associated with the performance.  The number of unique performances works because I am counting and can count unique ids, which will be the same no matter how many times the sli rows are duplicated, but I can't find a way to only sum once for each sli_no, at least in the context of configuring a list criteria row in T_KEYWORD.  Now obviously I can create a new view with a grouping, but then it won't be part of the same criteria set and you won't be able to use the criteria together.