Help with Date

Discussion in 'ASP General' started by Ben, Apr 22, 2004.

  1. Ben

    Ben Guest

    Im writing a script that will get the last 7 days of information, more
    specifically the points within those 7 days. What I have works some of
    the time, but it doesn't work all of the time, if a user hasn't
    entered anything for 7 days it crashes, thanks for any help.
    ------------------------------------------------------------------
    i = 0
    x = 7
    'Create an ADO recordset object
    Set rs_rufitPoints = Server.CreateObject("ADODB.Recordset")

    'Initialise the strSQL variable with an SQL statement to query the
    database
    strSQL = "SELECT points, actDate FROM tblActivity WHERE USER_ID = "&
    ret_ID &" ORDER BY actdate DESC"

    'Open the recordset with the SQL query
    rs_rufitPoints.Open strSQL, adoCon

    Do While NOT rs_rufitPoints.EOF
    intTotalRufitWeeklyPoints = 0
    intTempRufitPoints = 0
    tempDate = date() - i
    tempDate1 = date() - x
    Do While rs_rufitPoints("actDate") > tempDate1 and
    rs_rufitPoints("actDate") <= tempDate
    intRufitPoints = rs_rufitPoints("points")
    intTempRufitPoints = intTempRufitPoints + intRufitPoints
    rs_rufitPoints.MoveNext
    Loop
    If intTempRufitPoints >= 35 then
    intTempRufitPoints = 35
    End If
    i = i + 7
    x = x + 7
    intTotalRufitPoints = intTotalRufitPoints + intTempRufitPoints
    rs_rufitPoints.MoveNext
    Loop
    Response.Write("<br>Points Towards your RuFit Achievement
    Levels</strong>")
    Response.Write( intTotalRufitPoints )
    ------------------------------------------------------------------
    Ben, Apr 22, 2004
    #1
    1. Advertising

  2. Ben wrote:
    > Im writing a script that will get the last 7 days of information, more
    > specifically the points within those 7 days. What I have works some of
    > the time, but it doesn't work all of the time, if a user hasn't
    > entered anything for 7 days it crashes, thanks for any help.
    > ------------------------------------------------------------------
    > i = 0
    > x = 7
    > 'Create an ADO recordset object
    > Set rs_rufitPoints = Server.CreateObject("ADODB.Recordset")
    >
    > 'Initialise the strSQL variable with an SQL statement to query the
    > database
    > strSQL = "SELECT points, actDate FROM tblActivity WHERE USER_ID = "&
    > ret_ID &" ORDER BY actdate DESC"


    :)
    This is silly. If you want the last 7 days, then retrieve the last 7 days.
    What database are you using?

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 22, 2004
    #2
    1. Advertising

  3. Ben

    Ben Dunlap Guest

    Im using an access database. How do you retrive just the last 7 days?
    Also Im not just trying to retrive the last 7 days, I also need to keep
    going back 7 days until EOF.
    EX:

    4/22/04 -> 4/15/04
    4/15/04 -> 4/08/04
    4/08/04 -> 4/01/04
    etc....

    Better yet is if I could go Sun -> Sat
    So if today is Thursday it would do something like this.
    Thur -> Sat
    Sun -> Sat
    Sun -> Sat
    Then keep doing this until EOF

    Thanks!

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Ben Dunlap, Apr 22, 2004
    #3
  4. Ben Dunlap wrote:
    > Im using an access database. How do you retrive just the last 7 days?


    strSQL = "SELECT points, actDate FROM tblActivity WHERE " & _
    "actDate>= DateAdd(""d"",-7,Date()) AND USER_ID = "& _
    ret_ID &" ORDER BY actdate DESC"

    > Also Im not just trying to retrive the last 7 days, I also need to
    > keep going back 7 days until EOF.
    > EX:
    >
    > 4/22/04 -> 4/15/04
    > 4/15/04 -> 4/08/04
    > 4/08/04 -> 4/01/04
    > etc....
    >
    > Better yet is if I could go Sun -> Sat
    > So if today is Thursday it would do something like this.
    > Thur -> Sat
    > Sun -> Sat
    > Sun -> Sat
    > Then keep doing this until EOF


    I guess I totally misunderstood your question. What is it you are trying to
    do? Get a total of the points for each week?

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 22, 2004
    #4
  5. Bob Barrows [MVP] wrote:
    > Ben Dunlap wrote:
    >> Im using an access database. How do you retrive just the last 7 days?

    >
    > strSQL = "SELECT points, actDate FROM tblActivity WHERE " & _
    > "actDate>= DateAdd(""d"",-7,Date()) AND USER_ID = "& _
    > ret_ID &" ORDER BY actdate DESC"
    >
    >> Also Im not just trying to retrive the last 7 days, I also need to
    >> keep going back 7 days until EOF.
    >> EX:
    >>
    >> 4/22/04 -> 4/15/04
    >> 4/15/04 -> 4/08/04
    >> 4/08/04 -> 4/01/04
    >> etc....
    >>
    >> Better yet is if I could go Sun -> Sat
    >> So if today is Thursday it would do something like this.
    >> Thur -> Sat
    >> Sun -> Sat
    >> Sun -> Sat
    >> Then keep doing this until EOF

    >
    > I guess I totally misunderstood your question. What is it you are
    > trying to do? Get a total of the points for each week?
    >

    Well, it's pretty obvious that that's what you are trying to do.

    Do you have any objections to adding a Calendar table to your database? It
    would contain two columns: Weeknum (Long Integer) and RefDate
    (Date/time).I'm sure you'll find outher uses for it besides this particular
    report, especially if you add a column to store whether the date is a
    holdiay or workday or whatever.

    Anyways, if you can add a table, we can write a query that will give you
    your points per week totals very quickly. If not, we will need to continue
    with the cursor solution you've started.

    Let me know ...

    Bob Barrows


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 22, 2004
    #5
  6. Ben

    Ben Guest

    I just added a table called tblCalendar that looks like

    ID........(AutoNumber)
    Weeknum...(long int)
    RefDate...(Date/Time)

    What would I do now? Thanks!

    -=To email me take out the joke.=-

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Ben, Apr 23, 2004
    #6
  7. Ben wrote:
    > I just added a table called tblCalendar that looks like
    >
    > ID........(AutoNumber)
    > Weeknum...(long int)
    > RefDate...(Date/Time)
    >
    > What would I do now? Thanks!


    Make sure you have a unique index on the RefDate column (you can do this in
    Table Design)

    Are you familiar with using the VBA IDE in Access? We need to populate the
    table and it would be easiest doing it in Access. Step by step:
    Open the database in Access
    Click into the Modules tab
    Click the New button
    Paste this code into the code window (modify the dStart value so that it
    will include all the data in your database):
    '*********************************
    Option Explicit
    Sub PopulateCalendar()
    Dim dStart As Date
    Dim i As Long, j As Integer
    Dim sSQL As String
    dStart = #1/1/2000#
    Do Until Weekday(dStart) = vbSunday
    dStart = DateAdd("d", 1, dStart)
    Loop
    DoCmd.SetWarnings False
    For i = 0 To 2000
    For j = 1 To 7
    sSQL = "INSERT INTO tblCalendar" & _
    "(WeekNum, RefDate) VALUES (" & _
    i & ", #" & dStart & "#)"
    DoCmd.RunSQL sSQL, False
    dStart = DateAdd("d", 1, dStart)
    Next j
    Next i
    DoCmd.SetWarnings True
    End Sub
    ********************************
    Click into one of the lines in the procedure and press the F5 key to run it.
    When it finishes, you should have records in tbl Calendar up to 2038 - it
    should be enough?

    Now a grouping query can be used to get your weekly total points:

    SELECT Min(c.RefDate) AS WeekStart, Sum(a.Points) AS PointsPerWeek
    FROM tblActivity AS a INNER JOIN tblCalendar AS c ON a.actDate = c.RefDate
    WHERE a.UserID=1
    GROUP BY c.WeekNum

    You may wish to add start and end dates to the WHERE clause.

    HTH,
    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 23, 2004
    #7
  8. Ben

    Ben Guest

    That worked really well. Even though I didn't understand all of the
    code, Im going to have to go through it step-by-step this weekend so I
    can understand it.

    How do I go about printing the weekly total points? I tried
    Response.Write(PointsPerWeek); from reading the code I thought that,
    that is how I should be doing it but Im doing something wrong.

    Also, what does this snippet say?
    ----Min(c.RefDate) AS WeekStart----
    I think it means that you are taking Min(c.RefDate) and assigning it to
    WeekStart, but what does c.refDate mean?

    T/Y

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Ben, Apr 23, 2004
    #8
  9. Ben wrote:
    > That worked really well. Even though I didn't understand all of the
    > code, Im going to have to go through it step-by-step this weekend so I
    > can understand it.
    >
    > How do I go about printing the weekly total points? I tried
    > Response.Write(PointsPerWeek); from reading the code I thought that,
    > that is how I should be doing it but Im doing something wrong.


    Well, the "PointsPerWeek" is a column alias, meaning it's the name of the
    field in the recordset object. So you would do:

    Response.Write rs_rufitPoints("PointsPerWeek")

    (Why such a long variable name? What's wrong with rs? :) )

    >
    > Also, what does this snippet say?
    > ----Min(c.RefDate) AS WeekStart----
    > I think it means that you are taking Min(c.RefDate)
    > and assigning it to WeekStart


    Sort of. I am creating a column containing the result of Min(c.RefDate) for
    each WeekNum, and I am assigning a column alias, WeekStart, to be the name
    of that column.


    >, but what does c.refDate mean?


    "c" is a table alias. If you look in the FROM clause, you will see
    "tblCalendar AS c", which means that in this query, I can use c in place of
    the table name. So "c.RefDate" is the same as "tblCalendar.RefDate". IMO,
    table aliases make the sql more readable.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 23, 2004
    #9
  10. Ben

    Ben Guest

    Thanks for the explanation, I can understand much more of it now....and
    I like long varaible names :)

    When I run the code now I get this error:
    Too few parameters. Expected 1. activity_log.asp, line 989

    Here is the segment of code that it is pertaining to:
    -----------------------------------------------------
    Set rs_rufitPoints = Server.CreateObject("ADODB.Recordset")

    strSQL = "SELECT Min(c.RefDate) AS WeekStart, Sum(a.Points) AS
    PointsPerWeek FROM tblActivity AS a INNER JOIN tblCalendar AS c ON
    a.actDate = c.RefDate WHERE a.UserID="& ret_ID &" GROUP BY c.WeekNum"

    -=LINE 989=- rs_rufitPoints.Open strSQL, adoCon

    Response.Write rs_rufitPoints("points")
    Response.End()
    -----------------------------------------------------

    Also how does the strSQL statement know that
    c.RefDate is the same as tblCalendar.RefDate?

    -=To email me take out the joke.=-

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Ben, Apr 23, 2004
    #10
  11. Ben

    Bob Barrows Guest

    Show us what you get with

    Response.Write strSQL.

    Verify that you can run the statement that is written to the browser window
    in Access without modification.

    Bob Barrows

    Ben wrote:
    > Thanks for the explanation, I can understand much more of it
    > now....and I like long varaible names :)
    >
    > When I run the code now I get this error:
    > Too few parameters. Expected 1. activity_log.asp, line 989
    >
    > Here is the segment of code that it is pertaining to:
    > -----------------------------------------------------
    > Set rs_rufitPoints = Server.CreateObject("ADODB.Recordset")
    >
    > strSQL = "SELECT Min(c.RefDate) AS WeekStart, Sum(a.Points) AS
    > PointsPerWeek FROM tblActivity AS a INNER JOIN tblCalendar AS c ON
    > a.actDate = c.RefDate WHERE a.UserID="& ret_ID &" GROUP BY c.WeekNum"
    >
    > -=LINE 989=- rs_rufitPoints.Open strSQL, adoCon
    >
    > Response.Write rs_rufitPoints("points")
    > Response.End()
    > -----------------------------------------------------
    >
    > Also how does the strSQL statement know that
    > c.RefDate is the same as tblCalendar.RefDate?
    >
    > -=To email me take out the joke.=-
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Apr 23, 2004
    #11
  12. Ben

    Ben Guest

    I finally got it all to work, thankyou Bob! You have taught me a great
    deal. Here is the final code incase anyone else is interested.
    Hopefully it will be able to help someone else.
    -----------------------------------------------------------------
    <%
    x = 0 'changed from 7
    ppw = 0 ' Points Per Week
    'Create an ADO recordset object
    Set rs_rufitPoints = Server.CreateObject("ADODB.Recordset")

    'Initialise the strSQL variable with an SQL statement to query the
    database
    strSQL = "SELECT Min(c.RefDate) AS WeekStart, Sum(a.Points) AS
    PointsPerWeek, c.Weeknum AS weekNumber FROM tblActivity AS a INNER
    JOIN tblCalendar AS c ON a.actDate = c.RefDate WHERE a.USER_ID="&
    ret_ID &" GROUP BY c.WeekNum"

    'Open the recordset with the SQL query
    rs_rufitPoints.Open strSQL, adoCon

    Do While NOT rs_rufitPoints.EOF
    Response.Write("Points Per Week:")
    Response.Write("<strong>")
    If rs_rufitPoints("PointsPerWeek") <= 35 then
    ppw = rs_rufitPoints("PointsPerWeek")
    Else
    ppw = 35
    End If
    x = x + ppw
    Response.Write(ppw)
    Response.Write("</strong>")
    Response.Write("&nbsp; &nbsp; &nbsp; Week Number:")
    Response.Write("<strong>")
    Response.Write rs_rufitPoints("weekNumber")
    Response.Write("</strong>")
    Response.Write("<br>")
    rs_rufitPoints.MoveNext
    Loop

    Response.Write("<br> <strong>Points Towards your RuFit Achievement
    Levels</strong> &nbsp;")
    Response.Write(x)
    %>
    -----------------------------------------------------------------
    Ben, Apr 26, 2004
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    572
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,196
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    495
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    695
    Richard Heathfield
    Nov 8, 2003
  5. Matt
    Replies:
    11
    Views:
    392
    Aaron Bertrand [MVP]
    Nov 8, 2003
Loading...

Share This Page