Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

query for sum between dates from a date in past to current.

Trying to get count of all records per week from october to current.

this info will be used for a chart.

I have tried
SELECT     COUNT(*) AS weekcount
FROM         tbl1
WHERE     (DateEntered BETWEEN CONVERT(DATETIME, '2004-10-18 00:00:00', 102) AND CONVERT(DATETIME, '2004-10-22 00:00:00', 102))

but that does not give me the resultset that I am searching for.
I am trying to return values as
wk1 500
week2 700
and so on.
can anyone help figure this out.
1 Solution
add the GROUP BY Clause to your query.  e.g.


You might need to create a week column or view.
Or you can do it after the query. Cold Fusion has a Week function which returns the week number in the year.  You can sort the query in your cold fusion code.  I don't know which DBMS's have a week function if any, but in either case, we'd need to know which one your using in order to help you group by week :)
For example, if you're using MySQL, you could do:

Select Count(*) as weekcnt, week(DateEntered) as week1
from tbl1
GROUP BY week1

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

if you use group by then do not forget to use having with it. Where clause willl not work with it.
something like
Select Count(*) as weekcnt, week(DateEntered) as week1
from tbl1
GROUP BY week1
Where your condition.
SELECT  COUNT(*) AS record_count, DATEPART(wk, DateEntered) AS week_number
FROM     tbl1
<!--- place correct condition here; I assume there are only this year records in the tbl1---->
WHERE   Month(DateEntered)>=10  
GROUP BY DATEPART(wk, DateEntered)
jriver12Author Commented:
Ok got the query that I need :
<cfquery name="graphData" datasource="dsn">

SELECT CONVERT(varchar(10),DateAdd(day,1-(DatePart(weekday,DateEntered))+6,DateEntered),101) AS weekEnding,
     COUNT(*) AS weeklyTotal
FROM attendee
WHERE DateEntered BETWEEN '10/18/2003' AND '11/30/2004'
GROUP BY CONVERT(varchar(10),DateAdd(day,1-(DatePart(weekday,DateEntered))+6,DateEntered),101)
ORDER BY CONVERT(varchar(10),DateAdd(day,1-(DatePart(weekday,DateEntered))+6,DateEntered),101)

code for results
<cfset lastWeeklyTotal=0>
<cfoutput query="graphData">
<cfset the_count = weeklytotal>
  <cfset percentageOfChange = lastWeeklyTotal / weeklyTotal>
  <cfset lastWeeklyTotal = weeklyTotal>
  #weekEnding# = #the_count# (#percentageOfChange#% of change)<br/>

here are the results

10/23/2004 = 1057 (0% of change)
10/30/2004 = 800 (1.32125% of change)
11/06/2004 = 588 (1.36054421769% of change)
11/13/2004 = 195 (3.01538461538% of change)
11/20/2004 = 699 (0.278969957082% of change)
11/27/2004 = 485 (1.4412371134% of change)
12/04/2004 = 133 (3.64661654135% of change)

Now I am trying to display these results in a line chart, but I cant get the syntax correct to show the multiple lines and colors.
can you show how.

I think your initial question was answered and you should award points.
If you have another question I think you should put it in a new post.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now