FIFO transaction Result Calculation

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

SQL Server 2000 Forums

Transact-SQL (2000)

FIFO transaction Result Calculation

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2008-09-08 : 15:56:00

[code]
ID TRXDATE ACCT TRXTYPE PRICE QTY
1 12/1/2001 VCN b 2.5 100
2 12/2/2001 VCN b 3 100
3 12/3/2001 VCN b 3.5 75
4 12/3/2001 VCN b 3.2 100
5 12/6/2001 VCN s 6.5 100
6 12/7/2001 VCN b 6.9 100
7 12/15/2001 VCN s 7 50
8 12/16/2001 VCN s 8 50
9 12/16/2001 VCN s 9 150
10 7/8/2002 VCN s 6 125

Base on 1% Commission of Gain,(0, if Loss)I would like to
RETURN 17.675. This result is base on below:

Qty COst Sale Gain/Loss % Commission
100 250 650 400 0.01 4
50 150 350 200 0.01 2
50 150 400 250 0.01 2.5
75 262.5 675 412.5 0.01 4.125
75 240 675 435 0.01 4.35
25 80 150 70 0.01 0.7
100 690 600 -90 0.01 0[/code]

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-12 : 09:58:07

[code]DECLARE @Sample TABLE
(
ID INT,
Trxdate DATETIME,
Acct CHAR(3),
TrxType CHAR(1),
Price SMALLMONEY,
Qty INT
)

SET DATEFORMAT MDY

INSERT @Sample
SELECT 1, '12/1/2001', 'VCN', 'b', 2.5, 100 UNION ALL
SELECT 2, '12/2/2001', 'VCN', 'b', 3, 100 UNION ALL
SELECT 3, '12/3/2001', 'VCN', 'b', 3.5, 75 UNION ALL
SELECT 4, '12/3/2001', 'VCN', 'b', 3.2, 100 UNION ALL
SELECT 5, '12/6/2001', 'VCN', 's', 6.5, 100 UNION ALL
SELECT 6, '12/7/2001', 'VCN', 'b', 6.9, 100 UNION ALL
SELECT 7, '12/15/2001', 'VCN', 's', 7, 50 UNION ALL
SELECT 8, '12/16/2001', 'VCN', 's', 8, 50 UNION ALL
SELECT 9, '12/16/2001', 'VCN', 's', 9, 150 UNION ALL
SELECT 10, '7/8/2002', 'VCN', 's', 6, 125

SELECT IDENTITY(INT, 0, 1) AS RowID,
s.ID,
s.Price
INTO #Buy
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE v.Number >= 1
AND v.Number <= s.Qty
AND s.TrxType = 'B'
ORDER BY s.TrxDate

SELECT IDENTITY(INT, 0, 1) AS RowID,
s.ID,
s.Price
INTO #Sell
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE v.Number >= 1
AND v.Number <= s.Qty
AND s.TrxType = 'S'
ORDER BY s.TrxDate

SELECT Qty,
Cost,
Sale,
[Gain/Loss],
[%],
[%] * [Gain/Loss] AS Commission
FROM (
SELECT MIN(b.RowID) AS RowID,
COUNT(*) AS Qty,
COUNT(*) * MIN(b.Price) AS Cost,
COUNT(*) * MIN(s.Price) AS Sale,
COUNT(*) * MIN(s.Price) - COUNT(*) * MIN(b.Price) AS [Gain/Loss],
CASE SIGN(MIN(s.Price) - MIN(b.Price))
WHEN 1 THEN 0.01
ELSE 0.00
END AS [%]
FROM #Buy AS b
INNER JOIN #Sell AS s ON s.RowID = b.RowID
GROUP BY b.ID,
s.ID
) AS d
ORDER BY RowID

DROP TABLE #Buy,
#Sell[/code]

E 12°55'05.63"
N 56°04'39.26"

hai
Yak Posting Veteran

84 Posts

Posted - 2008-12-11 : 15:22:51

Hi Peso,

thank you very much for the solutions, it work great until the QTY reached 1000000. As you can see, the problem is that the amount of insert into temp table slow everything down. Any suggestion or advice how to speed this thing up with large Qty? thank you for all your help.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources