Member Avatar for 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.

:(:$

  • 2 Contributors
  • 1 Reply
  • 347 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by arrgh

Dani AI

Generated

For : because tblCategories and tblEmployee live in SQL Server and tblData is in Oracle, trying to force everything into a single ad-hoc SQL across engines is brittle unless a linked server or replication exists. Two practical, maintainable approaches are recommended: do most aggregation in the database that hosts the big dataset (Oracle) after bringing the small category mapping there, or aggregate in each DB and merge/pivot in ColdFusion.

A straightforward CF-centered workflow (simpler for CF beginners):

  • Run an aggregated Oracle query that returns EMPLOYEEID, CODEID and SUM(TOTALMINUTESWORKED) for the date range (group by EMPLOYEEID, CODEID).
  • Pull tblCategories and tblEmployee from SQL Server into CF (small tables, cached if needed).
  • Build an in-memory lookup mapping CODEID -> {GROUP, SUBGROUP}.
  • Iterate the Oracle aggregated rows and, using the lookup, accumulate per-employee totals by GROUP and by SUBGROUP; also accumulate production and non-production totals and grand total.
  • After accumulation convert minutes to hours, compute PRODUCTION + NONPRODUCTION, and calculate util% as PRODUCTION / TOTAL (guard against zero).
  • Render the final cross-tab table from the in-memory structure (stable column order can be driven from tblCategories GROUP values).

Example CF pseudocode showing the merge/accumulate idea:

<cfquery name="qOracleAgg" datasource="OracleDSN"> SELECT EMPLOYEEID, CODEID, SUM(TOTALMINUTES) AS MINS FROM tblData WHERE ... GROUP BY EMPLOYEEID, CODEID </cfquery>

<!-- build categories lookup from qCategories -->
<!-- loop qOracleAgg rows: find group/subgroup via lookup and add MINS to employeeTotals[EMPLOYEEID][group] and employeeTotals[EMPLOYEEID][subgroup] -->

<!-- post-process: convert mins to hours, compute totals and percent (handle divide-by-zero) -->

Practical notes and gotchas: aggregate as much as possible in the DB to reduce row volume; use cfqueryparam for parameters; handle missing CODEID mappings by assigning "Unknown"; SQL Server 2000 lacks a native PIVOT operator, so DB-side pivots there are usually implemented with SUM(CASE WHEN ... THEN ... ELSE 0 END) or with dynamically generated SQL. For very large data sets consider a scheduled ETL or materialized summary table to keep report runtime fast.

Member Avatar for 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/

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.