Solved

Reporting on Banner views and clicks

Posted on 2011-03-25
12
337 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

777 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