Help with Date

[Ben]

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

 

[Bob Barrows [MVP]]

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

 

[Ben Dunlap]

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!

 

[Bob Barrows [MVP]]

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

 

[Bob Barrows [MVP]]

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


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

 

[Ben]

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

 

[Bob Barrows [MVP]]

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

 

[Ben]

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

 

[Bob Barrows [MVP]]

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

 

[Ben]

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

 

[Bob Barrows]

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]

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