SQL Server Developer Center >
SQL Server Forums
>
SQL Server Tools General
>
query for proxy invoice
query for proxy invoice
Well I have 2 tables, one bidhistory and another products, bidhistory keeps a tab on all the bids and the product table just holds the bid made, ie: highest each time someone bids, my problem is when the auction is finished I need to get all bids that are proxy high ie:
finished bid was 25 but i bid 75 so it should look through the table find the highest bid and get the price below that because noone outbid my bid,, it sounds complicated to me
All Replies
Are you using SQL Server 2005 or higher? If so this sounds like an opportunity to use the rank feature.
- Proposed As Answer byDBAJDS Thursday, October 22, 2009 1:34 PM
- Sorry for delay, heres some more information Im using 2008 server - sounds like I need to create temp tables on custid and then get my information then delete tables not sure?>
1. I am looking through the above data when an item in a auction closes.
2. The item that ends using this code
sqlUpdate = "update products set listhidden = 1 where idproduct = "&pidProduct
3. Using that id idproduct I can then scan the data below by idproduct
id idCustomerBid idProduct bidAmount bidDate 144 1 268 45.00 2009-10-22 10:49:57.460 145 1 268 65.00 2009-10-22 10:50:00.803 146 1 271 25.00 2009-10-22 11:10:25.150 147 4 268 60.00 2009-10-22 12:13:13.320 148 4 268 80.00 2009-10-22 12:13:19.053 149 4 268 100.00 2009-10-22 12:13:22.267 137 1 268 25.00 2009-10-22 09:25:15.530 138 1 269 25.00 2009-10-22 09:26:08.463 139 1 272 25.00 2009-10-22 09:26:28.590 140 1 273 25.00 2009-10-22 09:30:15.520 141 4 268 25.00 2009-10-22 09:30:39.337 142 4 268 45.00 2009-10-22 09:30:44.393 143 4 276 75.00 2009-10-22 09:40:45.807
4. I then need to get the highbid of that item and return the highest bid but if there are multiple bids by other users I need the proxy bid (definition-
.... in which the winning bidder pays the price of the second-highest bid plus a defined increment.....) and the customers id
5. I then need to calculate all the bids by that user from the bidhistory table and repeat the search to create an invoice -
I hope this helps, Its a tall order I know but i need the automation applying, if there are other examples I would love to hear from anyone1. I am looking through the above data when an item in a auction closes.
2. The item that ends using this code
sqlUpdate = "update products set listhidden = 1 where idproduct = "&pidProduct
3. Using that id idproduct I can then scan the data below by idproduct
id idCustomerBid idProduct bidAmount bidDate 144 1 268 45.00 2009-10-22 10:49:57.460 145 1 268 65.00 2009-10-22 10:50:00.803 146 1 271 25.00 2009-10-22 11:10:25.150 147 4 268 60.00 2009-10-22 12:13:13.320 148 4 268 80.00 2009-10-22 12:13:19.053 149 4 268 100.00 2009-10-22 12:13:22.267 137 1 268 25.00 2009-10-22 09:25:15.530 138 1 269 25.00 2009-10-22 09:26:08.463 139 1 272 25.00 2009-10-22 09:26:28.590 140 1 273 25.00 2009-10-22 09:30:15.520 141 4 268 25.00 2009-10-22 09:30:39.337 142 4 268 45.00 2009-10-22 09:30:44.393 143 4 276 75.00 2009-10-22 09:40:45.807
4. I then need to get the highbid of that item and return the highest bid but if there are multiple bids by other users I need the proxy bid (definition-
.... in which the winning bidder pays the price of the second-highest bid plus a defined increment.....) and the customers id
5. I then need to calculate all the bids by that user from the bidhistory table and repeat the search to create an invoice -
I hope this helps, Its a tall order I know but i need the automation applying, if there are other examples I would love to hear from anyone - Moderator: this is a T-SQL question.
Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
