We have the following table structure:
/****** Object: Table [dbo].[BannerStats] ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[BannerStats](
[id] [int] IDENTITY(1,1) NOT NULL,
[bannerId] [int] NOT NULL,
[vchType] [varchar](8) NULL,
[dtmCreated] [datetime] NOT NULL
CONSTRAINT [PK_BannerStats] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
In this table, each row of data represents a view or a click of a banner, and the date/time it occurred.
vchType can be either "VIEW" or "CLICK". the other columns are pretty self-explanatory.
I need to generate a report on this table, using a ColdFusion 8 line or bar chart if possible. Along the bottom axis would be the days, the vertical axis would represent the number of views and clicks for that banner on that day. I could probably accomplish this with with several CFOUTPUT loops grouped by VIEW and CLICK, then by day, but I wonder if I can get the actual numbers per day right from the database query. The resulting output, with a WHERE clause filtering out to just one bannerID, would look something like this:
3/24/2011 | VIEW | 524
3/24/2011 | CLICK | 10
3/25/2011 | VIEW | 356
3/25/2011 | CLICK | 3
unless someone has a workable alternative that would still give me a recordset I can use to plot the bars/points on the graph, by day. Eventually the report query will also be fed a date range.
Any takers? :)