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

Posted on 2004-11-30
Last Modified: 2013-12-24
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.
Question by:jriver12

    Expert Comment

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

    GROUP BY Week

    You might need to create a week column or view.

    Expert Comment

    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 :)

    Expert Comment

    For example, if you're using MySQL, you could do:

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

    LVL 22

    Expert Comment

    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.
    LVL 7

    Accepted Solution

    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)

    Author Comment

    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.


    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
    Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 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

    13 Experts available now in Live!

    Get 1:1 Help Now