Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts

    How to list the only last record??? URGENT!!

    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
    Last edited by NinjaTurtle; 08-11-2004 at 02:40 PM.
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    dear,

    i cat add any fields, due to no any changes of database structure can be done!!!
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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>
    Last edited by NinjaTurtle; 08-11-2004 at 11:41 AM.
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #7
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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!!!!
    Last edited by NinjaTurtle; 08-11-2004 at 12:23 PM.
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #8
    New Coder
    Join Date
    Aug 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Do While Loop

    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

  • #9
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Dear GzArIa,

    I get the same result, the last record is not print out.
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #10
    New Coder
    Join Date
    Aug 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Different way!

    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

  • #11
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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)??
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #12
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #13
    New Coder
    Join Date
    Aug 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    total,

    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

  • #14
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    But the total should calculate 1st b4 it print out rite? Or when is the best time to print out the result?
    Last edited by NinjaTurtle; 08-11-2004 at 02:37 PM.
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #15
    New Coder
    Join Date
    Aug 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ...

    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,...


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •