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?
Msg 4109, Level 15, State 1, Line 206Windowed functions cannot be used in the context of another windowed function or aggregate.
Bah, I thought I had something there...