Charting recordcounts for each day of the month

[drizzage]

I'm using CF 5, and need to generate a graph of how many new records have been entered into the db for each day of the month. I also need to make sure that every day of the month is represented even if there are no new records to display for that day. I know that there must be a way to do this with cf, without having to create a dummy table to allow the query to enter a "0" for the days without records. This is what I've come up with:

<CFGRAPH TYPE="bar">
<cfloop index="x" from="1" to="#daysinmonth(now())#">
	<CFQUERY 
	Name="getRecords" 
	Datasource="mydb">
	SELECT *
	FROM myTable
	WHERE Day(DateEntered) = #x# and Month(DateEntered) = Month(GetDate()) and Year(DateEntered) = Year(GetDate())
	</CFQUERY>
	<CFIF getRecords.recordCount gt 0>
		<CFSET thisValue=getRecords.recordCount>
	<CFELSE>
		<CFSET thisValue=0>
	</CFIF>
	<CFOUTPUT>
		<CFGRAPHDATA ITEM="#x#" VALUE="#thisValue#">			
	</CFOUTPUT>
</CFLOOP>
</CFGRAPH>

This works, but is of course grossly inefficient since the query runs once for each day in the month. Idealy, the query should of run one time, and then be referenced in the loop. I don't know nearly enough about cf or sql to know how to accomplish this. Does anyone have any ideas?

Thanks in advance.

 

[r937]

start off by retrieving all the counts that you want from the table in one query, rather than retrieving the entire contents of the table for each day in a loop

<cfset first 
  = DateAdd("d",-Day(Now()+1,Now())>
<cfset last 
  = DateAdd("d",-1,
    DateAdd("m",1,
    DateAdd("d",-Day(Now()+1,now())))>
<cfquery Name="getCounts" Datasource="mydb">
  select Day(DateEntered) as theday
       , count(*) as thecount
    from myTable
   where DateEntered 
      between '#first#' 
          and '#last#'
   group by DateEntered          
</cfquery>

this will return a result set that looks like this --
1 324
2 153
4 167
5 341
...

now all you have to do is set up a loop to process this result

note Days

<CFGRAPH TYPE="bar">
<cfloop index="x" 
  from="1" to="#daysinmonth(now())#">
<CFIF x EQ getCounts.theday>
  <CFSET thisValue=getCounts.thecount>
<CFELSE>
  <CFSET thisValue=0>
</CFIF>
<CFOUTPUT>
  <CFGRAPHDATA ITEM="#x#" VALUE="#thisValue#">            
</CFOUTPUT>
</cfloop>
</CFGRAPH>

rudy

http://r937.com/

SQL Consulting

 

[drizzage]

Thanks a bunch for the help, you've definitely brought me one step closer. I've included your code, but I need to know how to pass the entire recordset to the loop. Right now only the first returned record is showing up on the graph. In other words, my result set looks like this:
1 1

 

[r937]

could you please show your query and your cfoutput

rudy

http://r937.com/

SQL Consulting

 

[drizzage]

This is my query:

<cfset first 
  = DateAdd("d",-Day(Now())+1,Now())>
<cfset last 
  = DateAdd("d",-1,
    DateAdd("m",1,
    DateAdd("d",-Day(Now())+1,now())))>
<cfquery Name="getCounts" Datasource="mydb">
  select Day(DateEntered) as theday
       , count(*) as thecount
    from myTable
   where DateEntered 
      between '#DateFormat(first)#' 
          and '#DateFormat(last)#'
   group by DateEntered          
</cfquery>

This is the graph code:

<CFGRAPH TYPE="bar" BARSPACING="5" DEPTH="3" FILEFORMAT="jpg" COLORLIST="red,blue" ITEMLABELSIZE="8" ITEMLABELORIENTATION="Horizontal" GRIDLINES="3">
<cfloop index="x" 
	  from="1" to="#daysinmonth(now())#">
	<CFIF x EQ getCounts.theday>
	  <CFSET thisValue=getCounts.thecount>
	<CFELSE>
	  <CFSET thisValue=0>
	</CFIF>
	<CFOUTPUT>
	  <CFGRAPHDATA ITEM="#x#" VALUE="#thisValue#">            
	</CFOUTPUT>
</cfloop>
</CFGRAPH>

And this is the graph being generated:

Only the first record from the first day is showing up.

 

[r937]

uh oh

my fault, sorry

replace --

<CFIF x EQ getCounts.theday>
<CFSET thisValue=getCounts.thecount>

with --

<cfset pos
= ListFind(ValueList(getCounts.theday),x)>
<CFIF x EQ pos>
<cfset thisValue = getCounts.thecount[pos]>





rudy

http://r937.com/

SQL Consulting

 

[drizzage]

Ahhh I get it! The list contains all of the days that need to be populated by the query. I just had to change "<CFIF x EQ pos>" to "<CFIF pos NEQ 0>", and it works! I sure do appreciate the help rudy. I'm curious about one thing though. You specified the date range with the "first" and "last" variables, and then had the query return records within this range. Is there a reason that you didn't just tell the query to retrieve everything from the current month?

"WHERE Month(DateEntered) = Month(GetDate()) and Year(DateEntered) = Year(GetDate())"

Is your way better? Faster? I'm trying to learn sql, and want to better understand the mechanics of your coding.

 

[r937]

yes, a date range for a date column will utilize an index defined on the column, i.e. very efficient, whereas the use of functions on the date column will mean that the index will not be used

as far as the CFIF is concerned, yes, you're right

my code-without-testing skills are pretty rusty today!

rudy

http://r937.com/

SQL Consulting

 

[drizzage]

Gotcha. The date field is not currently indexed in the db, but I may want to do that.

Thanks again for all your help!