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.