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.
jriver12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crazyuttyCommented:
add the GROUP BY Clause to your query.  e.g.

GROUP BY Week

You might need to create a week column or view.
0
crazyuttyCommented:
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 :)
0
crazyuttyCommented:
For example, if you're using MySQL, you could do:

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

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

pinaldaveCommented:
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.
0
INSDivision6Commented:
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)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)
</cfquery>

------------------------------------------------------
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/>
</cfoutput>

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.

0
omenchaosCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.