Building a Date Table with ColdFusion

If you have ever worked with a data warehouse, or a complex, date-sensitive application, then you have probably come across a date dimension table. Simply put, a date dimension table is a lookup table that contains one record per date. Each record has information about that date, such as the day of the week, the quarter it falls in, and so on. Many DBAs come armed with a SQL script that creates and populates a date dimension table. If you are not a SQL adept, why not use the power of ColdFusion?

Each record in a date dimension table describes a date, so our table will have the following columns:

  • date - the full date
  • year - the date's year
  • month - the date's month number
  • month_desc - the month's name
  • day - the date's day
  • quarter - the date's quarter within the year
  • day_of_week - the date's day of week number
  • day_of_week_desc - the day of week's name
  • day_of_year - the date's day number within the year
  • week_of_year - the date's week number within the year

In SQL Server:

view plain print about
2    [days_id] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
3    [date] smalldatetime NOT NULL,
4    [year] tinyint NOT NULL,
5    [month] tinyint NOT NULL,
6    [month_desc] varchar(10) NOT NULL,
7    [day] tinyint NOT NULL,
8    [quarter] tinyint NOT NULL,
9    [day_of_week] tinyint NOT NULL,
10    [day_of_week_desc] varchar(10) NOT NULL,
11    [day_of_year] tinyint NOT NULL,
12    [week_of_year] tinyint NOT NULL

Now that the table is created, it needs data. Populating the data is simply a big loop using the many different CF functions for splitting and formatting dates. In this case, I will generate a script that can then be run in a database query tool, such as Query Analyzer:

view plain print about
1<cfset currentDate = createDate(2000, 01, 01)>
2<cfset targetDate = createDate(2008, 12, 31)>
4<cfloop condition="dateDiff('d', targetDate, currentDate) LTE 0">
5    <cfoutput>
6        INSERT INTO days (date, year, month, month_desc, day, quarter, day_of_week, day_of_week_desc, day_of_year, week_of_year)
7        VALUES (
8            '#createODBCDate(currentDate)#',
9            #year(currentDate)#,
10            #month(currentDate)#,
11            '#monthAsString(month(currentDate))#',
12            #day(currentDate)#,
13            #quarter(currentDate)#,
14            #dayOfWeek(currentDate)#,
15            '#dayOfWeekAsString(dayOfWeek(currentDate))#',
16            #dayOfYear(currentDate)#,
17            #week(currentDate)#
18        )
19    </cfoutput>
20    <cfset currentDate = dateAdd("d", 1, currentDate)>

In the above example, the date dimension table will contain one record for each day between January 1, 2000 and December 31, 2008. In the real world you will probably use a longer timespan, depending on your needs.

DateCompare Date/Time Gotcha

After years of date wrangling I really should know better, but ColdFusions's dateCompare function still managed to trip me up today. As its name implies, dateCompare compares two dates and returns a -1 if the first date is before the second date, 0 if they are equal, or 1 if the first is after the second. The gotcha lies in what a date is in ColdFusion. A date is not just the month, day, and year, but also the time.

This code snippet illustrates my head-slapping moment:

view plain print about
1<cfset currentDate = now()>
2<cfset targetDate = createDate(2008, 04, 30)>
4<cfloop condition="dateCompare(currentDate, targetDate) LTE 0">
5    <cfoutput>#dateFormat(currentDate, "mm/dd/yyyy")#<br /></cfoutput>
6    <cfset currentDate = dateAdd("d", 1, currentDate)>

I wanted a list of days between today, April 23, and the end of the month. However, I ended up missing a day, April 30. The output of the above snippet is:


So where did that missing day go? Into the time portion of the date. DateCompare not only looks at the date portion of a date, but also the time portion.

view plain print about
1<!--- Returns 1 because the second date is midnight on April 23. --->
2<cfoutput>#dateCompare(now(), createDate(2008, 04, 23))#</cfoutput>

To accommodate the time element of a date, I changed the code to use the dateDiff function and examine only the day:

view plain print about
1<cfset currentDate = now()>
2<cfset targetDate = createDate(2008, 04, 30)>
4<cfloop condition="dateDiff('d', targetDate, currentDate) LTE 0">
5    <cfoutput>#dateFormat(currentDate, "mm/dd/yyyy")#<br /></cfoutput>
6    <cfset currentDate = dateAdd("d", 1, currentDate)>

Which outputs:


Problem solved, until next time. :)

Ranking Using Query of Queries

Many applications these days have some sort of standings table where you can see how you compare to other site members. Forums have them to show top posters, wikis have them to show top contributors, social networking sites have them to show who has the most connections. Google "SQL ranking" and you will get a long list of articles about how to build one of these tables using the database.

One common approach is the following query, which uses a self-join:

view plain print about
1SELECT, a.sales, COUNT(a.sales) AS rank
2FROM sales a, sales b
3WHERE a.sales < b.sales OR (a.sales = b.sales AND =
4GROUP BY, a.sales
5ORDER BY rank

In the interest of Friday fun, I decided to take a different route and completely remove the database from the equation. I used query of queries to create the recordset for a standings table for the Sky Blue Pink Pencil Club. Members in SBPPC collect rare pencils that are colored sky blue pink and are very proud of their collections. But who are the top pencil collectors?

Let's start by building a recordset of names and the size of their pencil collection:

view plain print about
2/* Create a recordset containing each member of the SBPPC club and the size of their pencil collection. */
3names = listToArray("Jason,Jennifer,Sophia,Gary,Sue,Greg");
4pencils = listToArray("10,15,20,40,50,20");
6totals = queryNew("");
7queryAddColumn(totals, "name", names);
8queryAddColumn(totals, "pencils", pencils);
10/* Make a copy of the query for use in the QoQ below. */
11copy = duplicate(totals);

Note the last line within the cfscript tags where we create a copy of the original query. Query of queries does not support table aliases, only column aliases. It does, however, support joining two query recordsets together, which is the behavior we want.

Now let's build the standings query. To determine rank in SQL, we do a self-join on the data and count the number of records ahead of the current record when listed in order.

view plain print about
1<cfquery name="standings" dbtype="query">
2    SELECT, totals.pencils, COUNT(totals.pencils) AS [position]
3    FROM totals, copy
4    WHERE totals.pencils < copy.pencils
5        OR ( = AND totals.pencils = copy.pencils)
6    GROUP BY, totals.pencils
7    ORDER BY [position]

When output, we see that Sue leads the pack, Gary comes in second, and Greg and Sophia are tied for third.

Sue	50	1
Gary	40	2
Greg	20	3
Sophia	20	3
Jenn	15	5
Jason	10	6

Overwrite Attribute in Fusebox

Note to self...the overwrite attribute in the Fusebox set verb does exactly that, it overwrites the existing value of the variable.

While working with a Fusebox application, I needed to pass the current fuseaction to the next one. So I crafted a URL like:

view plain print about
1<a href="#request.myself##xfa.delete#&return=#myfusebox.originalfuseaction#">Delete</a>

The delete fuseaction looks for the return value and relocates to that fuseaction. The only problem was, it didn't work. It relocated to the wrong fuseaction. In fact, it was the default return location (I'm reusing the same fuseaction for multiple views). Looking at the code:

view plain print about
1<fuseaction name="doDelete">
2    <set name="attributes.return" value="defaultList" overwrite="true" />
3    <do action="m.deleteRecord" />
4    <relocate url="#request.myself##myfusebox.thiscircuit#.#attributes.return#" addtoken="false" />

I realized my problem. Instead of paraming the attributes.return value, I was setting it. Changing the overwrite attribute to "false" fixed it right up. Mea culpa.

ColdFusion 8.0.1 Updater Available

Yippee! The 8.0.1 updater for ColdFusion is now available and it contains official support for OS X Leopard. Looks like I'll have some install fun tonight.

Browsing an ODT File with CFZip

I use for my home office suite. The only Microsoft Office product that is on one of my systems is Access 2000, and only because I use it for a personal-use application I have not gotten around to upgrading. uses the OpenDocument Format specification as its internal file format. ODF is an XML-based file format, so an document is a collection of XML files stored in a ZIP archive. What a perfect opportunity to get acquainted with the CFZip tag in ColdFusion 8!


CFQueryparam and NULLs

This topic is far from new, but I think it deserves another mention. The cfqueryparam tag has an optional attribute, null, that allows you to pass a NULL value to the database. Note, this is a SQL NULL, not the empty string that replaces a NULL value in ColdFusion. The ColdFusion docs state that the attribute accepts either a yes or no value. However, like many other tags in ColdFusion, a boolean works just fine, and has the benefit of creating easier to read code. I find

view plain print about
1<cfqueryparam value="#mystring#" cfsqltype="cf_sql_varchar" maxlength="5" null="#NOT len(mystring)#">
is clearer and easier to read than
view plain print about
1<cfqueryparam value="#mystring#" cfsqltype="cf_sql_varchar" maxlength="5" null="#yesNoFormat(NOT len(mystring))#">

CF by CF

I usually do not get the opportunity to use cffile to write files to the file system. Today, however, I got the chance to use action="write" while working on the beginnings of a code generator. The proof-of-concept code creates a CF file in a specific directory, much in the way Fusebox 5 generates its parsed files (in fact, Fusebox was my inspiration). The code generator uses a Java StringBuffer to store the contents of the file before writing it to the file system:

view plain print about
1<!--- Line feeds need a carriage return and a line feed character --->
2<cfset variables.crlf = chr(13) & chr(10)>
3<!--- A Java StringBuffer is more efficient than using a string variable. --->
4<cfset variables.contentBuffer = createObject("java", "java.lang.StringBuffer").init()>
5<!--- Add the contents of the file to the buffer. --->
6<cfset variables.contentBuffer.append('<cfset variables.message = "CF generated me!">').append(variables.crlf)>
7<cfset variables.contentBuffer.append('<cfoutput>##variables.message##</cfoutput>')>

Note the double pound signs inside the cfoutput tags. If I did not escape the pound signs by doubling them, CF would think variables.message is an actual variable that needed to be parsed. Once the content buffer contains the full contents of the file, cffile swings into action to create the physical CFM file:
view plain print about
1<!--- cffile needs an absolute path, so place the file in the current directory. --->
2<cfset variables.filePath = getDirectoryFromPath(expandPath("*.*"))>
3<cffile action="write" file="#variables.path#/myfile.cfm" output="#variables.contentBuffer.toString()#">
Opening up myfile.cfm we see:
view plain print about
1<cfset variables.message = "CF generated me!">

Query of Queries and an Insert

A good friend of mine, and fellow CFer, came to me with an interesting situation yesterday. She was trying to insert the contents of a query resultset into a database table. To do this she tried to use a query of queries, which failed with the message "Query Of Queries syntax error. Encountered "INSERT.[sic]" Exact code changed to protect the innocent:

view plain print about
1<cfquery name="update" dbtype="query">
2    INSERT INTO article_info (id, title)
3    SELECT id, title
4    FROM articles_query
where article_query is a dynamically created query object and article_info is a physical database table.

One aspect of the problem is that query of queries does not support SQL statements other than SELECT. The ColdFusion docs make this point, but not explicitly, when defining query of queries:

A query that retrieves data from a record set is called a Query of Queries. After you generate a record set, you can interact with its results as if they were database tables by using Query of Queries. Livedocs - ColdFusion 8 - Using Query of Queries

Another part of the problem is the dbtype="query" attribute in the query. This tells ColdFusion that we are working with a query of queries. Since article_info is not a query object, CF will not find it in memory.

The solution we worked out is to loop over the query object and build the single INSERT statement she wanted using a series of SELECT...UNION ALL statements:

view plain print about
1<cfquery name="update" datasource="#dsn#">
2    INSERT INTO article_info (id, title)
3    <cfoutput query="articles_query">
4        SELECT #id#, '#title#'
5        <cfif currentrow LT article_info.recordcount>UNION ALL</cfif>
6    </cfoutput>

The result of the output loop creates an INSERT statement that looks like:

view plain print about
1INSERT INTO article_info (id, title)
2SELECT 1, 'Article One' UNION ALL
3SELECT 1, 'Article Two' UNION ALL
5SELECT 10, 'Article Ten'

I understand on the surface why query of queries only support SELECT queries. A query object does not always originate from a database - tags such as cfdirectory and cfsearch create query objects. I'm still curious, however, about the inner workings of query of queries. Is it possible in theory to allow an INSERT or DELETE statement?

To Pound Or Not to Pound

ColdFusion is very easy to start learning. As a tag-based language, it is very approachable to anyone who is comfortable with HTML. One area where many CF novices have trouble, though, is with improper use of the pound sign (#), that little mark that tells the ColdFusion server to return the value of a variable. Novice CFers often overuse the pound sign, surrounding a variable name when it is not needed or even desirable.

I believe proper use of the pound sign can be distilled down to two basic rules. Pound signs are needed only in the following circumstances:

  • If the variable name is surrounded in quotes
  • If you want to write the value of the variable to the screen/document/email
The first rule is where many CF novices trip up. When working with a variable name inside a tag, pound signs are often not needed. Here are a few examples, with corrections, of improper pound sign usage (I have seen all of these when conducting code reviews):

view plain print about
2<cfset #myvar# = 1>
5<cfset myvar = 1>
8<cfset myvar = #listLen(mylist)#>
9<cfset myvar = #listLen(#mylist#)#>
12<cfset myvar = listLen(mylist)>

Previous Entries / More Entries