array 1

[discusmania]

hi guys.
1. I have 4 column in my db, which is ctgry,date_enter, date_close and issue.

2. using date_close, i'll know in which week the issue was closed:
workweek=cint(DateDiff("ww",#30-12-2000#,(results
("date_close&quot),1,1))

3. using date_enter and date_close i calculate time to close the issue by calculating the different between date_enter and date_close like this:
days=trim(dateDiff("d",results("date_enter&quot,results
("date_close&quot))+1

4. From ctgry column i'll know in what category the issue fall.

what i want to do is to list like this in a table:

Work week ctgry1 ctgry2 ctgry3
ww1 ac time ac time ac time
ww2 ac time ac time ac time


until current workweek where ac time is the accumulated time to close issue for each ctgry.

please help.

Thanks

 

[Mighty]

What about something like:

...connect to DB and create recordset results.....

Dim actotals(0,3)

do while not rs.EOF
workweek=cint(DateDiff("ww",#30-12-2000#,(results
("date_close&quot),1,1))
if ubound(actotals) = 0 then
redim actotals(1,3)
actotals(0,0) = workweek
if rs.Fields("ctgry&quot = 1 then
actotals(0,1) = actotals (0,1) + trim(dateDiff("d",results("date_enter&quot,results
("date_close&quot))+1
elseif results("ctgry&quot = 2 then
actotals(0,2) = actotals (0,2) + trim(dateDiff("d",results("date_enter&quot,results
("date_close&quot))+1
else
actotals(0,2) = actotals (0,2) + trim(dateDiff("d",results("date_enter&quot,results
("date_close&quot))+1
end if

else if the upper bound is not zero, search through the array using a for loop for the workweek. If you find it just add the accumulated totals on. If you don't find it, use redim to adjust the bounds of the array and add in the new workweek.

Sorry, but I'm busy and don't have time to write out all the code. Hopefully, you understand what I have written. I hope it helps a little. Respond if you don't understand.


Mise Le Meas,

Mighty

 

[discusmania]

thanks for the help. Actually, this is my first attemp to use array. and i not really sure how array work. so fa here is what i modify from you code:

dim actotals(0,5)

'response.write SQL
do while not results.eof
workweek=cint(DateDiff("ww",#30-12-2000#,(results("date_close&quot),1,1))

if ubound(actotals)=0 then
redim actotals(1,5)
actotals(0,0)=workweek
select case results("pro_ctgry&quot
case "DownTime"
actotals(0,1)=actotals(0,1)+trim(dateDiff("d",results
("date_enter&quot,results("date_close&quot))+1
case "Entitlement"
actotals(0,2)=actotals(0,1)+trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
case "Mischuck"
actotals(0,3)=actotals(0,1)+trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
case "NPI/NPR"
actotals(0,4)=actotals(0,1)+trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
case "QualityMfg"
actotals(0,5)=actotals(0,1)+trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
end select

else 'if the upper bound is not zero, i dont know how to proceed from here.


Please help....
Thanks so much

New in ASP....
Ron

 

[Mighty]

dim actotals(0,5)
dim loopcount, weekFound

'response.write SQL
do while not results.eof
workweek=cint(DateDiff("ww",#30-12-2000#,(results("date_close&quot),1,1))

if ubound(actotals)=0 then
redim actotals(1,5)
actotals(0,0)=workweek
select case results("pro_ctgry&quot
case "DownTime"
actotals(0,1)=actotals(0,1)+trim(dateDiff("d",results
("date_enter&quot,results("date_close&quot))+1
case "Entitlement"
actotals(0,2)=actotals(0,1)+trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
case "Mischuck"
actotals(0,3)=actotals(0,1)+trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
case "NPI/NPR"
actotals(0,4)=actotals(0,1)+trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
case "QualityMfg"
actotals(0,5)=actotals(0,1)+trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
end select
else
weekFound = false
for loopcount = 0 to ubound(actotals)
if actotals(loopcount,0) = workweek then
select case results("pro_ctgry&quot
case "DownTime"
actotals(loopcount,1)=actotals(loopcount,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "Entitlement"
actotals(loopcount,2)=actotals(loopcount,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "Mischuck"
actotals(loopcount,3)=actotals(loopcount,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "NPI/NPR"
actotals(loopcount,4)=actotals(loopcount,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "QualityMfg"
actotals(loopcount,5)=actotals(loopcount,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
end select
weekFound = true
end if
next
if weekFound = false then
redim Ubound(loopcount+1,5)
actotals(loopcount+1,0)=workweek
select case results("pro_ctgry&quot
case "DownTime"
actotals(loopcount+1,1)=actotals(loopcount+1,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "Entitlement"
actotals(loopcount+1,2)=actotals(loopcount+1,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "Mischuck"
actotals(loopcount+1,3)=actotals(loopcount+1,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "NPI/NPR"
actotals(loopcount+1,4)=actotals(loopcount+1,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "QualityMfg"
actotals(loopcount+1,5)=actotals(loopcount+1,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
end select
end if
end if

I know this is a bit messy and the suntax is probably not great but I hope it gives you an idea of what I am trying to do. If you have any hassles, let me know. Mise Le Meas,

Mighty

 

[discusmania]

Appreciate your help very much. but still got problem, when i run the code it give me errors says that:

Microsoft VBScript runtime error '800a000a'
This array is fixed or temporarily locked

/dept/AMT/Software/testproaction/averagetime.asp, line 35

where line 35 is:
redim actotals(1,5)

then if i comment the code it runs without errors. when i try to print some value using response.write it still give me nothing. where should i put the response.write command?


thanks so much, lets go for lunch....

 

[Mighty]

discusmania,

I have marked the lines that I have changes in bold red. See if this works:

dim actotals()
dim loopcount, weekFound

'response.write SQL
do while not results.eof
workweek=cint(DateDiff("ww",#30-12-2000#,(results("date_close&quot),1,1))

if ubound(actotals)=0 then
redim actotals(1,6)
actotals(0,0)=workweek
select case results("pro_ctgry&quot
case "DownTime"
actotals(0,1)=trim(dateDiff("d",results
("date_enter&quot,results("date_close&quot))+1
case "Entitlement"
actotals(0,2)=trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
case "Mischuck"
actotals(0,3)=trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
case "NPI/NPR"
actotals(0,4)=trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
case "QualityMfg"
actotals(0,5)=trim(dateDiff
("d",results("date_enter&quot,results("date_close&quot))+1
end select
else
weekFound = false
for loopcount = 0 to ubound(actotals)
if actotals(loopcount,0) = workweek then
select case results("pro_ctgry&quot
case "DownTime"
actotals(loopcount,1)=actotals(loopcount,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "Entitlement"
actotals(loopcount,2)=actotals(loopcount,2)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "Mischuck"
actotals(loopcount,3)=actotals(loopcount,3)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "NPI/NPR"
actotals(loopcount,4)=actotals(loopcount,4)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "QualityMfg"
actotals(loopcount,5)=actotals(loopcount,5)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
end select
weekFound = true
end if
next
if weekFound = false then
redim Preserve Ubound(loopcount+1,6)
actotals(loopcount,0)=workweek
select case results("pro_ctgry&quot
case "DownTime"
actotals(loopcount,1)=actotals(loopcount,1)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "Entitlement"
actotals(loopcount,2)=actotals(loopcount,2)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "Mischuck"
actotals(loopcount,3)=actotals(loopcount,3)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "NPI/NPR"
actotals(loopcount,4)=actotals(loopcount,4)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
case "QualityMfg"
actotals(loopcount,5)=actotals(loopcount,5)+trim(dateDiff("d",results("date_enter&quot,results("date_close&quot))+1
end select
end if
end if


I made a few minor changes apart from those in bold red. Just compare it to the last post. A major point would be that the array has 6 elements but it was only defined as having 5 - that might have been causing the problem. If you think that the redim is causing the problem, then just define the array initially as actotals(52,6) - i.e. 52 weeks in the year. Then leave out the "If ubound(actotals) = 0" section and just use the else clause.
Mise Le Meas,

Mighty