I'm sure there's a very simple answer for this....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I'm sure there's a very simple answer for this....

Author

Topic

asderex
Starting Member

10 Posts

Posted - 2007-11-12 : 17:47:41

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
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 17:56:14

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.

asderex
Starting Member

10 Posts

Posted - 2007-11-12 : 18:00:21

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 18:09:33

[code]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[/code]

E 12°55'05.25"
N 56°04'39.16"

asderex
Starting Member

10 Posts

Posted - 2007-11-12 : 18:10:42

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.

asderex
Starting Member

10 Posts

Posted - 2007-11-12 : 18:32:38

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources