loop total

[mthompo]

hi - this is more a logic issue than a programing one
it follows from my post yesterday thread333-1260817 whereby a link is written to the page if a value is higher than 0.

now this link is in a recordset loop and the total is worked out after the loop is completed so i am stuck.

i dont now how to get the total to the links before
the loop starts?

now your probably saying do the calculation in the sql
but it is more complicated than that because the calculation consists of three recordsets.

rs1hours + rs2hours + rs3hours - allowedhours = hoursleft

have thought of completing the page and refreshing it
to request the value?
this sounds messy - hope i make sense, i will post some code if anyone wants it

 

[Tarwn]

What about looing at this in another fashion. Why do you have three recordsets? Are you talking to three differant databases?

I didn't see your other post, so when you say your outputting data from three recordsets, do you mean in three seperate loop or all in one loop?
Do you one line totals for ach line or the actual total for the each recordset added together for each line?

I havesome thoughts on a solution or two, but it's going to depend on more background info.

-T

 

[DotNetGnat]

i would still say do the calculation at the sql query level...

i am sure you can combine the 3 recordsets into one...

please give us more information so that we can assist you better...

-DNG

 

[mthompo]

knew people were guna say this
taken me ages to get this page looking just the way i want
it and this is the last thing to do!!!lol

but seems like the page is fundamentally flawed and
rewriting the sql/rs is the only way to go

Tarwn - there is only one database
- there are separate loops because there are
3 html tables

sql statement 1 - for mot times

motsql1 = "SELECT mot.mott, mot.motslot, car.carid, car.reg, book.bookid, book.bookdate, book.waittick, cus.fname, cus.lname, cus.pcode, cus.cusid, mot.bayid, mot.motid, mot.dayid, mot.baytypeid, sum(stdwork.stdtime) AS stdtime "_
& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motid = book.slotid "_
& "AND book.bookdate = "&[datesql] _
& " LEFT JOIN car ON car.carid = book.carid "_
& "LEFT JOIN cus ON cus.cusid = car.cusid "_
& "LEFT JOIN stdworklink ON stdworklink.bookid = book.bookid "_
& "LEFT JOIN stdwork ON stdwork.stdworkid = stdworklink.stdworkid "_
& "WHERE mot.dayid ="&[motdaysql] _
& " AND mot.bayid =1 "_
& "AND mot.baytypeid =1 "_
& "GROUP BY mot.mott "_
& "ORDER BY mot.motid"

sql statement2 - for wait times

waitsql1 = "SELECT mot.mott, mot.motslot, car.carid, car.reg, book.bookid, book.bookdate, cus.fname, cus.lname, cus.pcode, cus.cusid, mot.bayid, mot.motid, mot.dayid, mot.baytypeid, sum(stdwork.stdtime) AS stdtime "_
& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motid = book.slotid "_
& "AND book.bookdate = "&[datesql] _
& " LEFT JOIN car ON car.carid = book.carid "_
& "LEFT JOIN cus ON cus.cusid = car.cusid "_
& "LEFT JOIN stdworklink ON stdworklink.bookid = book.bookid "_
& "LEFT JOIN stdwork ON stdwork.stdworkid = stdworklink.stdworkid "_
& "WHERE mot.dayid ="&[motdaysql] _
& " AND mot.bayid = 1 "_
& "AND mot.baytypeid =2 "_
& "GROUP BY mot.mott "_
& "ORDER BY mot.motid"

sql statement 3 - standard work

waitsql2 = "SELECT mot.mott, mot.motslot, car.carid, car.reg, book.bookid, book.bookdate, cus.fname, cus.lname, cus.pcode, cus.cusid, mot.bayid, mot.motid, mot.dayid, mot.baytypeid, sum(stdwork.stdtime) AS stdtime "_
& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motid = book.slotid "_
& "AND book.bookdate = "&[datesql] _
& " LEFT JOIN car ON car.carid = book.carid "_
& "LEFT JOIN cus ON cus.cusid = car.cusid "_
& "LEFT JOIN stdworklink ON stdworklink.bookid = book.bookid "_
& "LEFT JOIN stdwork ON stdwork.stdworkid = stdworklink.stdworkid "_
& "WHERE mot.dayid ="&[motdaysql] _
& " AND mot.bayid = 2 "_
& "AND mot.baytypeid =2 "_
& "GROUP BY mot.mott "_
& "ORDER BY mot.motid"

looking at it i feel stupid because there is a lot of unnecessary work going on - but the output was ok

below is the html/asp for one html table - the other two are pretty much the same

<table>
<tr> 
    <td colspan="3" class="user">MOT Booking <span class="ticksml">white=waiting</span></td>
</tr>
<tr> 
    <td colspan="3" class = mfitsml>Bay 1</td>
</tr>
<%stdworktotalmotbay1 = 0
Do While (Not rsmot1.Eof)
'rsmot1.movefirst
x_mottime1 = rsmot1("mott")
x_reg = rsmot1("reg")
x_bookdate = rsmot1("bookdate")
x_fname = rsmot1("fname")
x_lname = rsmot1("lname")
x_pcode = rsmot1("pcode")
x_carid = rsmot1("carid")
x_motid = rsmot1("motid")
x_bookid = rsmot1("bookid")
x_stdtime = rsmot1("stdtime")
x_waittick = rsmot1("waittick")

if x_waittick = "1" then
tickclass = " class=ticksml"
else
tickclass = " class=mfitsml"
end if
%>
<tr> 
    <td class = mfitsml> 
    <%response.write x_mottime1 %></td>
<%
hoursleft2 = 10
if isnull(x_carid) OR x_carid = "" then %>
     <td class = mfitsml>
<a href="<%if hoursleft2 > 0 then 
Response.Write "custaddmot.asp?motdate=" & Server.URLEncode(motdate) & "&motid=" & Server.URLEncode(x_motid)%>"
target="_blank" onClick="window.open(this.href, this.target,'height=500,width=480,status=no,resizable=no,scrollbars=no');return false;"
<% Else 
Response.Write "javascript:alert('No Hours left');" 
End If%>">
<span class="mfitsml">FREE</span>
</a>
<% else %>
     <td class = mfitsml><a href=<%Response.Write "custaddmot.asp?carid=" & Server.URLEncode(x_carid) & "&motdate=" & Server.URLEncode(motdate) & "&bookid=" & Server.URLEncode(x_bookid)& "&motid=" & Server.URLEncode(x_motid)%> target="_blank" onClick="window.open(this.href, this.target, 'height=500,width=480,status=no,resizable=no,scrollbars=no');return false;"> 
<span <%=tickclass%>><%response.write x_reg & " - " & x_fname & " " & x_lname%></span></a></td>
<%end if%> 
     </td>
        
     <td class="mfitsml"> 
<div align="right"> 
<% if isnull(x_stdtime) OR x_stdtime = "" then
x_stdtime = 0
end if 
response.write x_stdtime%>
</div>
     </td>
</tr>
<%rsmot1.MoveNext
stdworktotalmotbay1 = (stdworktotalmotbay1 + x_stdtime)
Loop
%>

 

[davejam]

rather than running through 3 different recordsets couldn't you bring all the data in within 1 recordset

waitsql = "SELECT mot.mott, mot.motslot, car.carid, car.reg, book.bookid, book.bookdate, cus.fname, cus.lname, cus.pcode, cus.cusid, mot.bayid, mot.motid, mot.dayid, mot.baytypeid, sum(stdwork.stdtime) AS stdtime "_
& "FROM mottimes AS mot "_
& "LEFT JOIN book ON mot.motid = book.slotid "_
& "AND book.bookdate = "&[datesql] _
& " LEFT JOIN car ON car.carid = book.carid "_
& "LEFT JOIN cus ON cus.cusid = car.cusid "_
& "LEFT JOIN stdworklink ON stdworklink.bookid = book.bookid "_
& "LEFT JOIN stdwork ON stdwork.stdworkid = stdworklink.stdworkid "_
& "WHERE mot.dayid ="&[motdaysql] _
& " AND ((mot.bayid = 1 AND mot.baytypeid =1) or "_
& " (mot.bayid = 1 AND mot.baytypeid =2) or "_
& " (mot.bayid = 2 AND mot.baytypeid =2)) "_
& "GROUP BY mot.mott "_
& "ORDER BY mot.motid"

declare some string variables
then kind of say (within your one loop)

if rs("bayid") = 1 and rs("baytypeid")=1 then
 varHTMLstring1 = all your html bits and bobs built up
 varHTMLstring1 = varHTMLstring1 & other html bits
elseif rs("bayid") = 1 and rs("baytypeid")=1 then
 varHTMLstring2 = all your html bits and bobs built up
if rs("bayid") = 1 and rs("baytypeid")=1 then
 varHTMLstring3 = all your html bits and bobs built up
end if

then slap each variable within table tags (or add them to your variables) then page is done
just simply

response.write varHTMLstring1
response.write varHTMLstring2
response.write varHTMLstring3

i think i went off the subject of your initial question, which i think needs better explaining in the sence of what you want it to do

just a thought, haven't dabbled with asp for a couple of months

cheers

dave j

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[mthompo]

thanks dave jam - guna try doing this way