Solved

Reporting on Banner views and clicks

Posted on 2011-03-25
12
335 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
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.​
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now