Query of queries or cross tab reports for coldfusion 8

[CFApprentice]

Hi there,

I’m very new to ColdFusion and I’m still going through the learning process. I have a report to create in CF and I don’t know how to start:

I have three tables in 2 databases:

tblEmployeID with EMPLOYEEID, EMPNAME, MGRName (this is in SQL 2000)

tblCategories with CODEID, CODE( ABOUT 100) , Description, GROUP (has 10 different codes, like ADMIN, BREAK, MEETING, LUNCH, PROCESSING, etc), SUBGROUP (this has only 2 PRODUCTION AND NON PRODUCTION) (this in SQL 2000)

the table above in the code has 100 rows, GROUP has 10 different names, and subgroup has only 2.

And tblData with EMPLOYEEID, CODEID, STARTDATETIME, ENDDATETIME, TOTALMINUTESWORKED, MANAGERNAME ( this is Oracle database).

Now, for the report I need to:

1 – rows with the EMPLOYEEID, then columns with the GROUP (about 10 columns).

2 – then create a column in the report to add the totals PER CODE for the GROUP.

3 – create another column in the same report to add the totals per code for SUBGROUP

4 – then calculated the totals hrs worked for PRODUCTION and NON PRODUCION

5 – add the PRODUCTION + NONPRODUCTION as total hours

6 – then calculate the percentage of util by dividing PRODUCTION totals / totals hours.

My main problem is to put all of the above in one SQL query and drop in ColdFusion and make it work, I have no idea how the create the report above, any help is appreciated.

:(:$

[arrgh]

First, I doubt you are going to get that all in one query and it is probably a mistake to try. (You'll probably end up writing it as a stored procedure). Second, what you have described is really has nothing to do with CF. It is almost entirely SQL. So you may have better luck asking in a db guru area.

That said, the usual way to simulate cross tab queries is with CASE statements. This article shows one option (simple cross tab query). I wouldn't use the second dynamic option listed. It's a little hairy for my tastes ...
http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/