Project Done!

Most of the time I try to avoid talking about my work. Often it just is not appropriate. However, many people have no idea what I do. I find solutions to computer problems with a focus on web application development and database design; usually my work is in content management, inventory control, and e-commerce/point of sales. I have bid projects on automation of assembly lines, and done work as diverse as writing C++ programs with the aid of Russian programmers (that app had many millions of downloads btw). I sometimes get hands on and build computers or troubleshoot and repair systems. Often, I find some of my solutions to problems both fun and really cool.

I just completed two fun projects that each tormented me with overruns on deadlines due to technical hurdles. The latter was simply a statistical report of some tracked performance on a website. The challenge in organizing and categorizing the data came down to database design choices previously made by the client including housing some of the data in a MS SQL database and some of the data in a MS Access database. A requirement was speed so the choice was made to place as much onus on the MS SQL database for preparing the data. Unfortunately, this came down only to using some aggregate functions to get the totals and unioning a couple of tables. I did have the pleasure of writing a neat little UDF to convert a list into some table results. Because of the way the aggregate functions had to be written, the data came out neither grouped appropriately and with some unavoidably duplicate data.

Fortunately, ColdFusion now has the ability to perform queries on queries including the joining of data across different datasources. So now that I had data appropriately summed albeit out of order and with some duplicates (as stated previously), I could write a query to pull the labels from the MS Access database. All labels and details were in Access; all statistical information, referred to only by id, was in SQL. Next I used a query of query to join the resultset from the stored procedure with the result set from the Access database and now the data started to resemble something meaningful; however, duplicate information still litered the results.

ColdFusion also allows for manually building a result set. I’ve seen the unfortunate growth in popularity of the term "fake query" which is a terrible misnomer. It is a manual query result set. You, the programmer, are programmatically reproducing a set of data that otherwise would have been delivered via a database engine. So, using a loop to step through our latest result set formed from the QoQ and logic within the loop, I create a result set that combines information from the semi-duplicated rows into a single row. In the process I wrote a slick little number using a structure to hold the grand totals for each segment that I could refer to mathmatically when outputing the result set. Then I simply display the results reaching into the structure for additional information when necessary.

Wahla! Greek.