SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 I'm sure there's a very simple answer for this....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asderex
Starting Member

New Zealand
10 Posts

Posted - 11/12/2007 :  17:47:41  Show Profile  Reply with Quote
Hi,

I'm trying to write a "GetQuote" procedure with a single @Quantity parameter. The purpose of the query is to sum the total cost (price * quantity) of a given (quantity) given the available "Sale" items. The sale table has the following structure:

[SellOrderID] [uniqueidentifier] DEFAULT (newid()),
[OrderDate] [datetime] DEFAULT (getdate()),
[UserID] [nvarchar](50) ,
[SellPrice] [float] ,
[SellQuantity] [int] ,
[QuantRemaining] [nchar](10) NULL,
**QuantRemaining is the sell quantity remaining after existing partial trades on the Sell Order - intentionally denormalised.

A given quantity may be satisfied by multiple 'Sell Orders' and these should be processed from the lowest price first.

e.g. give the following:
SellOrderID QuantRemaining Price
A 100 $2.00
B 300 $3.00
C 200 $1.75
D 350 $2.50

GetQuote 400 should return:
Sum((200*$1.75)+(100*$2.00)+(100*$2.50))

I am relatively new to the DB game and am having problems creating a SQL statement to do this. If anyone has a solution for this it would be greatly appreciated.

Thanks

Asderex

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  17:56:14  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
So you need to break the pricing out by the nearest quantremaining but where the total GetQuote does not use less than the minimum quant remaining?

quote:

A 100 $2.00
B 300 $3.00
C 200 $1.75
D 350 $2.50

GetQuote 400 should return:
Sum((200*$1.75)+(100*$2.00)+(100*$2.50))



How is the logic applied to get the prices here?

Why not 350 @2.50 and 50 @ 2.00?

and wouldn't the starting quantity be needed at some point to ensure that the quote doesn't exceed availability, and to ensure that the quantremaining was in line with the quote starting point less the Quantremaining price level?

Might need a UDF or sproc to do this...





Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

asderex
Starting Member

New Zealand
10 Posts

Posted - 11/12/2007 :  18:00:21  Show Profile  Reply with Quote
Hi again,

It should be running through in ascending Sell Price order i.e.
GetQuote 200 = Sum((200*$1.75))
GetQuote 123132 = Sum((200*$1.75)+(100*$2.00)+(350*$2.50)+(300*$3.00)+(Further Available Quantities * Price(in ascending price order) (Note: This would probably exceed all available orders so it should just return the sum of all Available quants * price. I hope that clarifies my problem. Let me know if you need further detail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/12/2007 :  18:09:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE (SellOrderID CHAR(1), QuantRemaining INT, Price SMALLMONEY)

INSERT	@Sample
SELECT	'A', 100, $2.00 UNION ALL
SELECT	'B', 300, $3.00 UNION ALL
SELECT	'C', 200, $1.75 UNION ALL
SELECT	'D', 350, $2.50

DECLARE	@ParamValue INT,
	@Sum SMALLMONEY,
	@Items INT

SELECT	@ParamValue = 400,
	@Sum = 0

SELECT	@Items =	CASE
				WHEN @ParamValue > 0 AND QuantRemaining <= @ParamValue THEN QuantRemaining
				WHEN @ParamValue > 0 AND QuantRemaining > @ParamValue THEN @ParamValue
				ELSE 0
			END,
	@ParamValue = @ParamValue - @Items,
	@Sum = @Sum + @Items * Price
FROM	(
		SELECT		TOP 100
				QuantRemaining,
				Price
		FROM		@Sample
		ORDER BY	Price
	) AS d

SELECT	@Sum



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

asderex
Starting Member

New Zealand
10 Posts

Posted - 11/12/2007 :  18:10:42  Show Profile  Reply with Quote
This is a very basic trading DB that lets participants lodge a 'Sell Order' at a price that they decide. e.g. I am willing sell 200 shares for $3.00 each.... someone else maybe willing to 150 shares for $2.80 each.
When a purchaser wants buy 300 shares the query should return the cost starting at the minimum price and working up from there.
Go to Top of Page

asderex
Starting Member

New Zealand
10 Posts

Posted - 11/12/2007 :  18:32:38  Show Profile  Reply with Quote
Thank you so much!! It was a bit more complicated than I thought but your solution works. I would have been sitting here for a LONG time without your help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000