Solved

Reporting on Banner views and clicks

Posted on 2011-03-25
12
336 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 44
Caste datetime 2 52
coldfusion grab more the one form field to make an insert 3 30
Getting max record but maybe not use Group BY 2 18
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now