Working with Loops in ASP

[duck1973]

I have a data base with entrys like the one below.

¦ Name ¦ Number ¦ Date ¦
¦ Jane ¦ 2 ¦ 7/10/03 ¦
¦ Jim ¦ 2 ¦ 7/10/03 ¦
¦ Jane ¦ 2 ¦ 7/11/03 ¦
¦ Jim ¦ 2 ¦ 7/11/03 ¦
¦ Jane ¦ 2 ¦ 7/25/03 ¦
¦ Jim ¦ 2 ¦ 7/25/03 ¦
¦ Jane ¦ 2 ¦ 7/26/03 ¦
¦ Jim ¦ 2 ¦ 7/26/03 ¦

I want to run a SQL query so that it will sum the number by week for a indivial person. So for example if I wanted the weekly totals for jane. they would show up like this

¦ Name ¦ SUM Number ¦ Week of ¦
¦ Jane ¦ 4 ¦ 7/6/03 - 7/12/03 ¦
¦ Jane ¦ 4 ¦ 7/20/03 - 7/26/03 ¦

I have a submit form where you choice the name and the start date and and the number of weeks you want to show. I can get it to show the first week but then the loop keeps showing the first week and I cannot get the date to update to look at the next week during the loop. I am a little lost on how to update the date in the SQL query so that it will look at the next week.

[korkus2000]

What does your loop look like?

[webdevsf]

Your table:

create table People
(
PERSON_NAME varchar(50),
PERSON_DATE datetime,
PERSON_COUNT int
)

Create a new table called dateranges

create table DateRanges
(
DATE_START datetime,
DATE_END datetime
)

insert into DateRangs(DATE_START,DATE_END)
values('7/6/03','7/12/03')

insert into DateRangs(DATE_START,DATE_END)
values('7/13/03','7/19/03')

(you'll have to write routine that fills this in with the dates you need)

and then use a query that looks like:

select DATE_START,DATE_END,PERSON_NAME,sum(PERSON_COUNT)
from
People P inner join
DateRanges DR on P.PERSON_DATE>=DR.DATE_START and P.PERSON_DATE<=DR.DATE_END
group by DR.DATE_START,DR.DATE_END,P.PERSON_NAME

HTH-
WDSF

[duck1973]

The SQL I have is

SELECT sum(TotBLT) as SumTotBLT, sum(BLTTouched) as SumBLTTouched, sum(Closed) as SUMClosed, avg(RawResPercent) as AVGRawResPercent
FROM Table1
WHERE AgentName LIKE 'varagentname' AND BLTDate BETWEEN #varstartdate# AND #varstartdate# + 6

The Loop Statment is

<% Do %>
<tr>
<td>Week Of <%=week1__varstartdate%> thru <%=DateAdd("d",6,week1__varstartdate)%> </td>
<td><%=(week1.Fields.Item("SumTotBLT").Value)%></td>
<td><%=(week1.Fields.Item("SumBLTTouched").Value)%></td>
<td><%=(week1.Fields.Item("SUMClosed").Value)%></td>
<td><%=(week1.Fields.Item("AVGRawResPercent").Value)%></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<% week1__varstartdate = DateAdd("d",7,week1__varstartdate) %>
<% weeknumber = weeknumber - 1%>
<% loop until weeknumber = 0%>
_________________________________________

The Varable week1__varstartdate is from the form that contains the first date of the first week I want to search. I need this date to be moved up 7 days using the date add function to be used in the SQL query in the next loop. The weeknumber comes from the submit form telling how many weeks you want to show. that would count down untill it reachs 0 then the loop stops.

[garann]

It looks like you're just reading the first record over and over? I don't know how your data is coming out of the database, but if you're putting it in a recordset, you do something like

<% while recordset.next()

' code goes here

end while %>

Does that help any?