A stat page can be used for an e-store, blog, ad rotater, or any other type of application where you want to track a specific series of "things" that happen each day. For example, on a blog you might track blog hits. In an e-store, you might track daily sales. For an ad rotator you might track click throughs. This article will show how to write a query to group the daily activity in the first section, and how to display that as a graph using ColdFusion in the second section. The article will use SQL Server syntax, but could be adapted to any database. Also, the SQL code is applicable to any server model -- not only ColdFusion. This is part 1 of 2.

Preparation

For this article, I will assume you have a table that has an item that you need to break out into a daily statistic. The table should have one field that is a datetime field, recording a date/time that an event occurs. The event could be a page view, a click through, a sale of an item, or any other type of activity that would generate a row in your table. For the purposes of the article, I'll assume the following structure:

CREATE TABLE myItems (
myitemsid int IDENTITY (1, 1) NOT NULL ,
myItem varchar (50) NULL ,
myItemDate datetime NULL
)

ALTER TABLE myItems ADD
CONSTRAINT DF_myItems_myItemDate DEFAULT (getdate()) FOR myItemDate

and some sample data to start with:

INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-10 5:30PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test2','2006-09-10 2:20PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-10 3:30AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-09 12:01PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test2','2006-09-09 10:03AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-08 6:00PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-08 5:30AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-08 12:07AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-08 1:00PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test2','2006-09-08 3:00PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-07 3:30PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-07 4:00AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test2','2006-09-06 7:31PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-05 9:30PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-04 9:30PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-04 8:31AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-04 8:39PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-03 7:32AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-03 4:30PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test2','2006-09-02 3:34PM')

The basic premise is that we want to build a graph or show a summary data list of the number of items on each day. We'll build the query next.

Query

Using GROUP BY, you can create summaries of data. The following demonstrates a simply GROUP BY to determine the number of times different data was inserted into the myItem field:

SELECT myItem, COUNT(myItem) as TheCount
FROM myItems
GROUP BY myItem
ORDER BY COUNT(myItem) DESC

This gives us a result like this, using the data above:

MyItem TheCount
Test 15
Test2 5

How can we modify this to show data per day? You might try this:

SELECT myItem, myItemDate, COUNT(myItemDate) as TheCount
FROM myItems
GROUP BY myItem, myItemDate
ORDER BY COUNT(myItemDate) DESC

However, this gives you an undesirable result:

myItem myItemDate TheCount
Test2 34:00.0 1
Test 32:00.0 1
Test 30:00.0 1
Test 31:00.0 1
Test 39:00.0 1
Test 30:00.0 1
Test 30:00.0 1
Test2 31:00.0 1
Test 00:00.0 1
Test 30:00.0 1
Test 07:00.0 1
Test 30:00.0 1
Test 00:00.0 1
Test2 00:00.0 1
Test 00:00.0 1
Test2 03:00.0 1
Test 01:00.0 1
Test 30:00.0 1
Test2 20:00.0 1
Test 30:00.0 1

That is because there is a time tagging along with the date -- you can't group your data based on date only unless you separate the date from the time. The following is one way to accomplish this, using the SQL functions MONTH, DAY, and YEAR:

SELECT myItem,
MONTH(myItemDate) AS TheMonth,
DAY(myItemDate) AS TheDay,
YEAR(myItemDate) AS TheYear,
COUNT(*) AS TheCount
FROM myItems
GROUP BY myItem, DAY(myItemDate), MONTH(myItemDate), YEAR(myItemDate)
ORDER BY YEAR(myItemDate), MONTH(myItemDate), DAY(myItemDate)

Now, we get the result we want -- the data grouped by month, day, and year, with counts for each day:

myItem TheMonth TheDay TheYear TheCount
Test2 9 2 2006 1
Test 9 3 2006 2
Test 9 4 2006 3
Test 9 5 2006 1
Test2 9 6 2006 1
Test 9 7 2006 2
Test 9 8 2006 4
Test2 9 8 2006 1
Test 9 9 2006 1
Test2 9 9 2006 1
Test 9 10 2006 2
Test2 9 10 2006 1

We could also eliminate the different values in the myItem field by simply leaving it out of the query:

SELECT MONTH(myItemDate) AS TheMonth,
DAY(myItemDate) AS TheDay,
YEAR(myItemDate) AS TheYear,
COUNT(*) AS TheCount
FROM myItems
GROUP BY DAY(myItemDate), MONTH(myItemDate), YEAR(myItemDate)
ORDER BY YEAR(myItemDate), MONTH(myItemDate), DAY(myItemDate)

This gives us a summary of the whole table by month, day, and year:

TheMonth TheDay TheYear TheCount
9 2 2006 1
9 3 2006 2
9 4 2006 3
9 5 2006 1
9 6 2006 1
9 7 2006 2
9 8 2006 5
9 9 2006 2
9 10 2006 3

One last thing we need to do is to create a user-friendly "date" field, dropping the time. This is simply a matter of concatenating the day, month, and year, but first casting them into varchar data:

CAST (MONTH(myItemDate) as varchar(2)) + '/' +
CAST (Day(myItemDate) as varchar(2)) + '/' +
CAST(YEAR(myItemDate) as varchar(4)) as TheFullDate

This composite field also has to be included in the GROUP BY clause. The full query now looks like this:

SELECT MONTH(myItemDate) AS TheMonth,
DAY(myItemDate) AS TheDay,
YEAR(myItemDate) AS TheYear,
COUNT(*) AS TheCount,
CAST (MONTH(myItemDate) as varchar(2)) + '/' +
CAST (Day(myItemDate) as varchar(2)) + '/' +
CAST(YEAR(myItemDate) as varchar(4)) as TheFullDate
FROM myItems
GROUP BY DAY(myItemDate), MONTH(myItemDate), YEAR(myItemDate),
CAST (MONTH(myItemDate) as varchar(2)) + '/' +
CAST (Day(myItemDate) as varchar(2)) + '/' +
CAST(YEAR(myItemDate) as varchar(4))
ORDER BY YEAR(myItemDate), MONTH(myItemDate), DAY(myItemDate)

Graphing the Data Using ColdFusion

To show this data as a simple bar graph, you can use the <cfchart> tag in ColdFusion. First, set up the <cfquery> tag:

<cfquery name="rsStats" datasource="#mydsn#">
SELECT MONTH(myItemDate) AS TheMonth,
DAY(myItemDate) AS TheDay,
YEAR(myItemDate) AS TheYear,
COUNT(*) AS TheCount,
CAST (MONTH(myItemDate) as varchar(2)) + '/' +
CAST (Day(myItemDate) as varchar(2)) + '/' +
CAST(YEAR(myItemDate) as varchar(4)) as TheFullDate
FROM myItems
GROUP BY DAY(myItemDate), MONTH(myItemDate), YEAR(myItemDate),
CAST (MONTH(myItemDate) as varchar(2)) + '/' +
CAST (Day(myItemDate) as varchar(2)) + '/' +
CAST(YEAR(myItemDate) as varchar(4))
ORDER BY YEAR(myItemDate), MONTH(myItemDate), DAY(myItemDate)
</cfquery>

Then, set up a <cfchart> tag:

<cfchart format="flash"
xaxistitle="Day"
yaxistitle="Hits Per Day"
chartwidth="600">
<cfchartseries type="bar"
query="rsStats"
itemcolumn="TheFullDate"
valuecolumn="TheCount" />
</cfchart>

If you browse the page, you will see a graph based on the data:


Figure 1: Graph based on the data from the GROUP BY month, day, and year

Conclusion

Summary queries are easy to write once you know a few basic tips for dealing with date/time data. Part one of this series showed a few basic techniques for grouping by date only. Part 2 will show one way to fill in some missing days, and how to dynamically adjust the scales on the graph based on how much data you have.