Solved

Reporting on Banner views and clicks

Posted on 2011-03-25
12
340 Views
Last Modified: 2012-05-11
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
Comment
Question by:funaroma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 35218350
> 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
 

Author Comment

by:funaroma
ID: 35257581
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 35258406

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:funaroma
ID: 35258553
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 35259006


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
 

Author Comment

by:funaroma
ID: 35259705
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 35259783
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
 

Author Comment

by:funaroma
ID: 35259885
yes, dtmCreated contains a full OdbcDateTime / timestamp.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35259918

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
 

Author Comment

by:funaroma
ID: 35260204
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
 

Author Comment

by:funaroma
ID: 35260522
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
 

Author Closing Comment

by:funaroma
ID: 35260537
Persistence is a key personality trait of a helpful Expert; this expert was very persistent and provided exactly what I needed.
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question