Hello
Can somebody please tell me if there would be a more efficient way to do this. Rather than have two select statements.
I am wanting to select the end furtherest date of some items to display, but there may be some items that end on earlier days. What I have written works but seems to hang up the server a little bit.

strSQL_FindEndDate ="SELECT MAX(SaleEnd) AS SaleEndDate FROM DBProducts " &_
                "WHERE StoreVersion = 'MyStore' " &_
                "AND '" & Date & "' BETWEEN SaleStart AND SaleEnd " &_
                "GROUP BY SaleEnd ORDER BY SaleEnd DESC;"

strSQL = "SELECT Category, Product, Price FROM DBProducts " &_
                "WHERE StoreVersion = 'MyStore' " &_
                "AND '" & Date & "' BETWEEN SaleStart AND SaleEnd " &_
                "ORDER BY Category, Product;"

    Set objEndDate = objConn.Execute(strSQL_FindEndDate)
    Set objProduct = objConn.Execute(strSQL)


    Response.Write objEndDate("SaleEndDate") & "--Sale Ends<BR>"

        Do While Not objSelect.EOF

                Response.Write objProduct("Category") & "</B><BR>"
                Response.Write objProduct("Prod_Item") & "</B><BR>"
                Response.Write objProduct("Price") & "<BR><BR>"

        objSelect.MoveNext
        Loop

Thanks In Advance
Mike

__________________
Peace
Mike
http://www.eclecticpixel.com

what's objSelect ?

Are you just looking for the latest date inside the objProduct Recordset? If so, you can keep a single tracker inside the loop to find out the latest date. Try something like this:

1. Drop the objEndDate Recordset.
2. Change objSelect to objProduct
3. Add the SaleEnd column to your objProduct SQL statement
4. Change the loop like this: This code checks the value of the SaleEnd column. If its value is larger than the value in the LatestDate variable, the value from SaleEnd is stored in LatestDate. This way, LatestDate will always contain the, eumm, eeuh, latest date.

If SaleEnd can be null as well, you may need to do some checking and casting for this code to run properly.

HtH,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks for your help. The objSelect was a typo, sorry about that. When I try Imars solution I am getting the following results, thats one reason I tried the 2 selection solution.

Sale Ends
   Product
   Product
Sale Ends
   Product
   Product

What I am after is:
Lastest Date From Selection
    Listing Of All Products

Thanks
Mike

I may be missing something, but I think my solution can lead to the results you need.
Just don't output the results of the recordset, but instead add them to a string. Then at the end:

Response.Write(LatestDate)
Response.Write(AllProductsString)

If you need to display a lot of records, this may not work good, because string concatenation is very slow in ASP. You may end up with a slower solution than executing two SQL statements

If this is not what you mean, can you elaborate a little about your requirements?

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Now that would would work, I didn't think of that. Unfortunatley there are going to be anywhere from 80 to 100 records at a time. So that may not be good, I will test them both.

I appreciate your suggestions they really help alot. I do have some other places I can use that code.

Thanks
Mike

You're welcome.

Depending on the size of each field, I think executing a second statement will be quicker. After all, you don't have the overhead for creating and opening a connection, since you already have one open.

Testing these kind of scenario's is always the best way to find out. Peter (planoie) posted this recently: http://p2p.wrox.com/topic.asp?TOPIC_ID=10231

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.