• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

Reporting on Banner views and clicks

We have the following table structure:

USE [my-database]
GO
/****** Object:  Table [dbo].[BannerStats]     ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
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
(
      [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
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

etc...

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? :)

0
funaroma
Asked:
funaroma
  • 7
  • 5
1 Solution
 
gdemariaCommented:
> I wonder if I can get the actual numbers per day right from the database query

sure, just do a group in the query...

select banner_id, vchType, dtmCreated, count(*)
 from bannerStats
group by banner_id, vchType, dtmCreated


if dtmCreated is date with TIME, then you just need to format it so it's the DAY only  (or MONTH only if you want to show the counts per month).  Be sure to format it in both the select and group by



0
 
funaromaAuthor Commented:
I need to be able to report across multiple months, I am having trouble determining how to format and sort against dtmCreated... can you provide a specific working example?
0
 
gdemariaCommented:

If you're doing it by month, then you can break the date into year and month, so the result will be..

524   VIEW    2010   12   1,200  (december of 2010 has 1,200 views for banner 524)
524   VIEW    2011   01   1,350  (january of 2011 has 1,350 views for banner 524)


select banner_id, vchType, year(dtmCreated) theYear, month(dtmCreated) theMonth, count(*)
 from bannerStats
group by banner_id, vchType,  year(dtmCreated), month(dtmCreated)
order by year(dtmCreated), month(dtmCreated)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
funaromaAuthor Commented:
I'm showing a line chart that lists month and day along the horizontal access, and views/click counts on the vertical axis.  The horizontal axis range could be from any month and day to any other month and day.
0
 
gdemariaCommented:


select banner_id, vchType, dtmCreated, count(*)
 from bannerStats
where dtmCreated between #createODBCdate(form.stateDate)# and #createODBCdate(form.endDate)#
group by banner_id, vchType,  year(dtmCreated), month(dtmCreated)
order by dtmCreated

if you want to format the date..

CONVERT(VARCHAR(10), dtmCreated, 102) AS  dtmCreated
0
 
funaromaAuthor Commented:
The code below gives this error:

Column 'BannerStats.dtmCreated' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT
		bannerId,
		vchType,
		dtmCreated,
		COUNT(*) AS intTotal
	FROM
		BannerStats
	WHERE
		bannerId = <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
	GROUP BY
		bannerId,
		vchType,
		YEAR(dtmCreated),
		MONTH(dtmCreated)
	ORDER BY
		dtmCreated

Open in new window

0
 
gdemariaCommented:
I accidently left the year() and month() functions in the group by

select banner_id, vchType, dtmCreated, count(*)
 from bannerStats
where dtmCreated between #createODBCdate(form.stateDate)# and #createODBCdate(form.endDate)#
group by banner_id, vchType,  dtmCreated
order by dtmCreated

You never mentioned if your dtmCreated date field contains time..

If so, you will need to format it...

CONVERT(VARCHAR(10), dtmCreated, 102) AS  dtmCreated

in every place you use dtmCreated in the select...
0
 
funaromaAuthor Commented:
yes, dtmCreated contains a full OdbcDateTime / timestamp.
0
 
gdemariaCommented:

since the time is included, you need to convert it to day-only (as shown above)

If you group by the date including the time, you will get a different record for every minute of the day..
0
 
funaromaAuthor Commented:
Close to working... last question... is it possible to use WITH ROLLUP to get a total number of views and clicks for the date range?  Will post final solution shortly.
0
 
funaromaAuthor Commented:
Final solution, including ROLLUP and graph, appears below.  Thank you gdemaria.
<!--- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ --->
<!--- report init; controller should call model --->

<!--- retrieve all banner views and clicks for the specified banner and date range, with summary --->

<cfquery name="rsBannerData" datasource="#request.dsn">

	SELECT
		bannerId,
		vchType,
		CONVERT(VARCHAR(10), dtmCreated, 102) AS dtmCreated,
		COUNT(id) AS intTotal
	FROM
		BannerStats
	WHERE
		bannerId = <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">		
			AND
		dtmCreated BETWEEN #CreateOdbcDate(dtmReportStart)# AND #CreateOdbcDate(dtmReportEnd)#

	GROUP BY
		bannerId,
		vchType,
		CONVERT(VARCHAR(10), dtmCreated, 102)
			WITH ROLLUP
	ORDER BY
		vchType,
		dtmCreated

</cfquery>

<!--- show a bar graph if there's only a day's worth of data or less --->

<cfif rsBannerData.RecordCount LTE 2>
	<cfset graphType = "bar">
<cfelse>
	<cfset graphtype = "line">
</cfif>


<!--- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ --->
<!--- markup (invalid html doc; wrap with appropriate HTML/layout; controller should call view ) --->

<cfchart format="flash"
					xaxistitle="Day"
					yaxistitle="Total"
					backgroundColor="##7A7676"
					dataBackgroundColor="##BAB6B6"
					foregroundColor = "##FFFFFF"
					show3D="false" showlegend="true" chartwidth="720" chartheight="600">

		<cfoutput query="rsBannerData" group="vchType">

			<cfif vchType IS "VIEW">

				<cfchartseries type="#graphType#" serieslabel="Views" seriescolor="red">
					<cfoutput>
						<cfif dtmCreated NEQ "">
							<cfchartdata item="#DateFormat(dtmCreated,'m/d/yyyy')#" value="#intTotal#">
						<cfelse>
							<cfset intViewTotal = intTotal>
						</cfif>
					</cfoutput>
				</cfchartseries>

		<cfelseif vchType IS "CLICK">

			<cfchartseries type="#graphType#" serieslabel="Clicks" seriescolor="green">
				<cfoutput>
					<cfif dtmCreated NEQ "">
						<cfchartdata item="#DateFormat(dtmCreated,'m/d/yyyy')#" value="#intTotal#">
					<cfelse>
						<cfset intClickTotal = intTotal>
					</cfif>
				</cfoutput>
			</cfchartseries>

		</cfif>

	</cfoutput>

</cfchart>


<cfoutput>

	<p align="center">

		<br />
		<strong>TOTALS:</strong>
		<br />
		Views: #intViewTotal#
		<br />
		Clicks: #intClickTotal#
	</p>

</cfoutput>

Open in new window

0
 
funaromaAuthor Commented:
Persistence is a key personality trait of a helpful Expert; this expert was very persistent and provided exactly what I needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now