unordained wrote:

>> 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...

I'm too set in my ways which is why I need a kick in a different direction ;)

http://lsces.co.uk/lsces/wiki/index.php?page=Rep-QueueServe
For example
Goes back quite a few years now, and still working, but it uses FastReports in
Builder6 and I've moved most of the stuff to web based in PHP. Reports are the
last area to sort out, and while I have the simple stuff these multi-level
ones are more of a problem. The ORIGINAL designs were broken up to get fast
response - with IB5.0 ....

( The image links on the website have been messed up by a 'software
improvement' so only a couple of the reports have the right pictures :( )

--
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