Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Help with Date

Reply
Thread Tools

Help with Date

 
 
Ben
Guest
Posts: n/a
 
      04-22-2004
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 )
------------------------------------------------------------------
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      04-22-2004
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.


 
Reply With Quote
 
 
 
 
Ben Dunlap
Guest
Posts: n/a
 
      04-22-2004
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!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      04-22-2004

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.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      04-22-2004
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.


 
Reply With Quote
 
Ben
Guest
Posts: n/a
 
      04-23-2004
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!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      04-23-2004
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.


 
Reply With Quote
 
Ben
Guest
Posts: n/a
 
      04-23-2004
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!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      04-23-2004
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.


 
Reply With Quote
 
Ben
Guest
Posts: n/a
 
      04-23-2004
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!
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date, date date date.... Peter Grison Java 10 05-30-2004 01:20 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP General 11 11-08-2003 11:24 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP .Net 1 11-08-2003 09:14 PM
Given a date, how to find the beginning date and ending date of that week Matt C Programming 3 11-08-2003 09:07 PM
Given a date, how to find the beginning date and ending date of that week Matt C++ 2 11-08-2003 08:30 PM



Advertisments