FIFO transactions result

[huong30]

Hi all,
I have the following:

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

 

[SQLSister]

And the issue you have would be?

"NOTHING is more important in a database than integrity." ESquared

 

[huong30]

Hi SqlSister,

ID       date           ACCTN   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								

id	trxtype	cost	qty		id	trxtype	cost	qty
1	b	2.5	100		5	s	6.5	100
2	b	3	100		7	s	7	50
3	b	3.5	75		8	s	8	50
4	b	3.2	100		9	s	9	150
6	b	6.9	100		10	s	6	125

Base on First In First Out: This is the result								
buy qty	Buy cost	Sale qty	Sale cost					
2.5	100	6.5	100					
3	50	7	50					
3	50	8	50					
3.5	75	9	75					
3.2	75	9	75					
3.2	25	6	25					
6.9	100	6	100					

This is the result of the cost and sale gain QTY * Cost			
	buy	sale	gain
	250	650	400
	150	350	200
	150	400	250
	262.5	675	412.5
	240	675	435
	80	150	70
	690	600	-90

This is the result of 1% on the gain: 0 if loss on transaction			

	400	0.01	4
	200	0.01	2
	250	0.01	2.5
	412.5	0.01	4.125
	435	0.01	4.35
	70	0.01	0.7
	-90	0.01	0

Want this Value			17.675

 

[SQLSister]

and what sql do you have?

"NOTHING is more important in a database than integrity." ESquared

 

[huong30]

Hi Sqlsister,

Base on the table, I like to create a function to return a table that with the break down. I'm a bit lost as how to write this sql...

thanks


ID date ACCTN 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




CREATE FUNCTION dbo.GetStockItems(@ItemNum int)
RETURNS @OrderTable table
(
OrderID int IDENTITY(1,1),
cost money,
buyqty int,
salecost money,
saleqty int

)
AS
BEGIN
INSERT @OrderTable


*****need help qith sql *********
cost1 buyqty cost2 saleqty
2.5 100 6.5 100
3 50 7 50
3 50 8 50
3.5 75 9 75
3.2 75 9 75
3.2 25 6 25
6.9 100 6 100
*********************************
RETURN

END