Here's the scenario: I'm building a new criteria set for ticketing information, and one of the things I want to add is Production Element Keywords. Now, this creates lots of duplicate rows, as multiple keywords can be attached to a single performance (not to mention packages! Almost forgot to do those!) Still as a List Critieria that's mostly no problem as I'm trying to pull customer_nos at the end of the day. But! There are a few fields where I want to be able to search on an aggregate value. One is # of Unique Perfs: that one is easy, COUNT(distinct perf_no) is going to work no matter how many times the SLI row is inflated. But I'd also love to have a "Paid Amount" criteria as well. I'd need something like the SUM of the MAX of each (sli_no, keyword_id) grouping? Does anyone know a clever way to get this, or will SUM and sli_no duplication always be incompatible?
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.