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
-----------------------------
Contact - http://lsces.co.uk/lsces/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php