> I think that Common Table Expression is a solution to a report problem I'm
> trying to solve, but I could do with some help understanding how to create the
> query.
>
> Someone with a few more grey cells may see an alternative may to go anyway.
>
> The report has sections for each department, and each section returns the
> number of 'transactions' below limit A, between limit A and limit B and above
> limit B. Three transaction bands defined by two time settings. The time
> settings change on a department by department basis. And there are ten
> departments. The limits are stored in another table.
>
> One further complication is that we normally report the results as percentages
> of the total for a department. So we need the total and a count for each
> band and show them as percentages. We also need to report the longest
> transaction for each department since if very long this is often an error and
> can be deleted - to massage the figures ;)
>
> Even as I'm writing this I'm seeing other ways of doing it The current reports
> simply scan the transaction table - ordered by department - counts the
> transactions in each band - and produce a result manually. I'm sure that it
> should be possible to produce the same result in SQL so I can just put it into
> the simple report generator I've got working already. That displays a grid
> with the results in and allows the user to dump as CSV if they need to do
> further processing.
>
> Reason I'm thinking CTE is that the examples are showing exactly the sort of
> thing I want to do but based on sales targets and the like. Am I safe using
> the examples for MSSQL for playing with these?
>
> --
> Lester Caine - G8HFL

From comparing the CTE's I write for FB with the ones my colleague writes for MSSQL, I'd say yes,
the examples are similar enough to be useful. However almost all CTE examples seem to be for
recursion, and from your problem statement, it looks like you'd really only be using them to help
clean up an otherwise messy and redundant query, but even then that seems like it might be
overkill. Maybe I didn't catch the recursive part of the problem statement? Are departments in a
hierarchy, perhaps?

If you would like someone to have a go at helping write the query, could you post the relevant
table structures and a closer approximation of what you want the report to look like (ascii art)? I
can't tell if you intend your final report to include some transaction details, or just group-by
information about the transactions.

If you're worried about doing math in a group-by SELECT where some calculated values depend on
others, you could nest a simple group-by in another select that does the extra math required
(percentages, for example). You could do that as a CTE to make the statement cleaner, but it's not
required. (Derived tables are cool, but I'd rather see them done as CTEs, with comments to indicate
what each block is attempting to do.)

Also, you have the option of taking your current control-break processing logic, putting it into a
selectable stored procedure, and calling that from your reporting system, yes? Even if you can't
write it as a simple query, you could still make it appear to be one from a reporting standpoint...

-Philip