...

View Full Version : How to list the only last record??? URGENT!!



NinjaTurtle
08-11-2004, 09:16 AM
CustID OfficeID QTY
======+=====+===
0001 AB1 1
0001 XY3 2
0002 AB1 1
0003 PC5 4
0003 DD2 2
0003 LO4 1


how to get result like:
0001 3
0002 1
0003 7

Do WHile rs.eof
.
.
. ' record should listed here
.
rs.movenext
Loop

raf
08-11-2004, 09:31 AM
you need to add a timestamp or autonumcolumn and then select on that inside a subquery.

like (supposing you add an autonum named custofficePK)

select CustID, OfficeID FROM table WHERE custofficePK In (select max(custofficePK) from table GROUP BY CustID)

NinjaTurtle
08-11-2004, 10:14 AM
dear,

i cat add any fields, due to no any changes of database structure can be done!!!

raf
08-11-2004, 10:30 AM
then you have nothing to order or run an agregationfunction (the max()) on, and then it is simply impossible. A database where you can't add a column too --> i can't think of any reason why this would cause problems.

NinjaTurtle
08-11-2004, 10:39 AM
Dear,

Actually i solve part of the problem using the following code... i can get the result, ONLY the last record will not come out due to the While ..Loop. The last record will not print out, or when there is only 1 Supplier in the database.




<%

strCondition = "Yes"
set rs = server.createobject("adodb.recordset")
sql = pfuncGenerateSQL("v_MYAMS1103SupplierDeliveryPunctuality",field,symbol,value)
sql = sql & " Order by [Supplier ID] "

rs.activeconnection = connection
rs.cursorlocation = 3
rs.cursortype = 2
rs.locktype = 3
if sql <> "false" then
rs.open sql
end if

%>

<html>
<title>Suppliers Delivery Punctuality Report</title>
<body>

<table border="0" width="640" align="center" bordercolor="white" cellspacing="0" cellpadding="1" class="text-a-12">
<tr>
<td>

<table width="100%" border = "1" bordercolor="white" cellspacing="0" cellpadding="1" class="text-a-12">
<br>
<tr bordercolor="white" bgcolor="white">
<td><b>Customer Name</b></td>
<td><b>Within Delivery</b></td>
<td><b>Late Delivery</b></td>
<td><b>Total PO</b></td>
<td><div align="right"><b>% Within Delivery</b></div></td>
<td><div align="right"><b>% Late Delivery</b></div></td>
</tr>
<%
SID2=""
PunctualDelivery=0
counterCnt=0


Do while not rs.eof
SID=rs("Supplier ID")
NoOfPO=rs("Total PO")
tempCounter=counterCnt
tempPunctualDelivery=PunctualDelivery
tempSupplierID=SID2
tempCountPO=countPO


if SID<>SID2 and counterCnt<>0 then

%>
<tr bordercolor="white">
<td align="left"><%=tempSupplierID%>&nbsp;</td>
<td align="left"><%=tempPunctualDelivery%>&nbsp;</td>
<td align="left"><%=tempCountPO - tempPunctualDelivery %>&nbsp;</td>
<td align="left"><%=tempCountPO%>&nbsp;</td>
<td align="right"><%=formatnumber((tempPunctualDelivery/tempCountPO)*100)%>&nbsp;</td>
<td align="right"><%=formatnumber(((tempCountPO - tempPunctualDelivery)/tempCountPO)*100)%>&nbsp;</td>
</tr>
<%

countPO=0
PunctualDelivery=0

end if


if IsNull(rs("PO Within Delivery")) then
PO_Within_Delivery = 0
else
PO_Within_Delivery = rs("PO Within Delivery")
end if

countPO=countPO+NoOfPO
PunctualDelivery=PunctualDelivery+PO_Within_Delivery


if SID=SID2 then
counterCnt=counterCnt+1
else
counterCnt=counterCnt-1
end if
SID2=SID

rs.MoveNext
loop

%>
</table>

</tr>
</table>
</body>
</html>

raf
08-11-2004, 10:52 AM
Maybe you just think you solved it...

I see you ordering on the supplierId no --> wasn't in you initial post.

In your initial post, the problem is to order within the CustID. If this can not be done by ordering on the 'OfficeID' (the alphabetically first or last, which is probably not what you want), then you are stuck. Unless this supplierID is the ordering you need to get the last record inside each CustID but then i don't see the point of your initial post ...

If you don't order within the OrderID, then you can not be sure how the records will e ordered inside each OrderID. They might look the same , but if you run the script a few hundred times and then you'll see that not all records are the same, which makes it useless. You should never assume that the records will be ordered in some manner without explicitely setting it in an order-clause.

NinjaTurtle
08-11-2004, 11:01 AM
Dear,

For my initial post is because that is another report with same problem. sorry to confuse u. The asp source code is the table result like:

[Supplier ID] [Total PO] [PO Within Delivery]
----------------------------------------------
AMMCSB 1 1
AMMCSB 2 2
AMMCSB 1 1
AMMCSB 1 1
DMDBB 3 <NULL>
DMDBB 1 1
T3M001 17 1
T3M001 16 1



The code is run fine and that is what i want but only i cant get the LAST RECORD!!!!

GzArIa
08-11-2004, 11:14 AM
Hi there,

I think your problem is the Do while loop

as in this loop the test is done on the end compared to the While Wend loop, the loop understands, after you do the last .movenext, that you are at the end of the recordset,

to solve it, instead of using
do while not rs.eof

loop

use
while not(rs.eof)

wend
laters
GzArIa

NinjaTurtle
08-11-2004, 11:21 AM
Dear GzArIa,

I get the same result, the last record is not print out.

GzArIa
08-11-2004, 11:35 AM
Hi there again,

what you could do to test it as well is,...

after you setup the recordset use an array with the getrows function,...

get rid of the other stuff (as it might be an if condition leaves that record out) before and use this function straight after you ran your SQL query,...

if the last record is still missing, then there must be a problem with your SQL statement

arrayrecordset = rs.GetRows()

for x= 0 to Ubound(arrayrecordset,2)
Response.Write arrayrecordset(0,x)
next

should list you the first column (probably ID) from your SQL query,...

laters
GzArIa

NinjaTurtle
08-11-2004, 11:41 AM
Yup, i get all the correct rows of record.... include the last 3 records(Same Supplier ID, but print once) that missing...

so i doubt is there anything wrong with my comparison of SID(Supplier ID)??

NinjaTurtle
08-11-2004, 11:49 AM
How to do the calculation of the accumulate of the [Total PO] in the GetRows?

for x= 0 to Ubound(arrayrecordset,2)
SID=arrayrecordset(0,x)
NoOfPO=arrayrecordset(3,x)
if SID<>SID2 then
Response.Write "<BR>"&X&". "&arrayrecordset(0,x)
end if
SID2=SID
next

GzArIa
08-11-2004, 12:48 PM
well you can't make a total with a function, but you could make the total by adding all figures up during the loop

totalOfPO = 0
for x= 0 to Ubound(arrayrecordset,2)
SID=arrayrecordset(0,x)
NoOfPO=arrayrecordset(3,x)
if SID<>SID2 then
Response.Write "<BR>"&X&". "&arrayrecordset(0,x)
end if
SID2=SID
totalOfPO = totalOfPO + NoOfPO
next

NinjaTurtle
08-11-2004, 01:08 PM
But the total should calculate 1st b4 it print out rite? Or when is the best time to print out the result?

GzArIa
08-11-2004, 01:59 PM
well i assume that the total is put on the bottom of the table,

so leave everything as it is, and add another table row, or output the total after the looping through the recordset,...

NinjaTurtle
08-11-2004, 04:49 PM
dear, all the record must be in a single row.

that's mean :

[Supplier ID ] [Total PO]

Roy Sinclair
08-11-2004, 10:46 PM
This should do the trick:



<%
SID2=""
PunctualDelivery=0
counterCnt=0
Do while not rs.eof
SID=rs("Supplier ID")
NoOfPO=rs("Total PO")
tempCounter=counterCnt
tempPunctualDelivery=PunctualDelivery
tempSupplierID=SID2
tempCountPO=countPO
if SID<>SID2 and counterCnt<>0 then
call Output_A_Row
end if
if IsNull(rs("PO Within Delivery")) then
PO_Within_Delivery = 0
else
PO_Within_Delivery = rs("PO Within Delivery")
end if
countPO=countPO+NoOfPO
PunctualDelivery=PunctualDelivery+PO_Within_Delive ry
if SID=SID2 then
counterCnt=counterCnt+1
else
counterCnt=counterCnt-1
end if
SID2=SID
rs.MoveNext
loop
if counterCnt <> 0 then
call Output_A_Row
end if
Sub Output_A_Row
%>
<tr bordercolor="white">
<td align="left"><%=tempSupplierID%>&nbsp;</td>
<td align="left"><%=tempPunctualDelivery%>&nbsp;</td>
<td align="left"><%=tempCountPO - tempPunctualDelivery %>&nbsp;</td>
<td align="left"><%=tempCountPO%>&nbsp;</td>
<td align="right"><%=formatnumber((tempPunctualDelivery/tempCountPO)*100)%>&nbsp;</td>
<td align="right"><%=formatnumber(((tempCountPO - tempPunctualDelivery)/tempCountPO)*100)%>&nbsp;</td>
</tr>
<%
countPO=0
PunctualDelivery=0
end sub
%>


Do you see how this fixes the problem? The problem is caused by the fact that your current code only outputs a row for the previous supplier when the first record for the next supplier is provided, however for the last supplier there is no "next" supplier. The code I provided moves the print line into a sub (so you don't have to have duplicate code) and makes sure that the last supplier gets printed out.

NinjaTurtle
08-12-2004, 02:50 AM
Dear,


Thank You!!!!Thank You Everyone here!!!! :thumbsup: :D
BUT...
i get the last row of record is same with the second last record.

Eg:

0001
0002
0003
0003
--end of record--
SO just change all the tempSupplierID, tempPunctualDelivery...tempxxxx back to normal variable (SID2--not SID, PunctualDelivery,..)... it's works WELL...


Thank You!!!!Thank You!!!!Thank You!!!!Thank You!!!!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum