matching records - fifo - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

matching records - fifo

Author

Topic

yeskay2008
Starting Member

7 Posts

Posted - 2009-09-24 : 21:35:51

I have requirement that requires FIFO logic and adds matching in and out records
base on code.

maintable

code
buydate
qty
rate
buyorsell

AAA,12-10-08,10,1000.00,B
BBB,13-10-08,8,750.00,B
AAA,14-10-08,5,1100.00,S
AAA,20-10-09,4,800.00,S
BBB,14-10-09,3,800.00,B

Here 'AAA' has 10 bought and 9 sold.
a). find the price differece between each matching buy and sell based on code
but split by sell qty.
Here
code, buyqty, buydate,buy price,sellqty,selldate,profit (buy-sell)*qty, (buydate-selldate)
AAA ,5, 12-10-08,1000,5,14-10-08,1100,(100)*5 , 2days
AAA ,4, 12-10-08,1000,4,20-10-09,800,(-200)*4, 370days

AAA has still 1 qty in balance that can be used for further computation.

b). In this if the date difference between
buy and sell is greater than 1 year then i have to calculate tax on the profit.

pls give pointers on solving this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-25 : 15:01:40

so for each sell record you want all this detail? if yes,


SELECT t.code, t1.qty AS buyqty, t1.buydate,t1.rate AS [buy price],t.qty AS sellqty,t.buydate AS selldate,t.rate AS [sell price],
(t.rate-t1.rate)* t.qty AS [profit],DATEDIFF(dd,t1.buydate,t.buydate)
FROM YourTable t
CROSS APPLY (SELECT TOP 1 *
             FROM YourTable
             WHERE buyorsell='B'
             AND code=t.code
             AND buydate<= t.buydate
             ORDER BY buydate DESC) t1
WHERE t.buyorsell='S'

yeskay2008
Starting Member

7 Posts

Posted - 2009-09-27 : 11:55:21

Thanks for the pointer, this works if there is 1 buy and multiple sells.
i.e if there are mulitple buys and sells the sell records needs to be mapped
on buy records on FIFO.

create table yourtable (code char(3),buydate datetime,qty int,rate int,buyorsell char(1))

insert into yourtable values('AAA','2009-09-21 12:26:06.243',100,1000,'B')
insert into yourtable values('AAA','2009-09-22 12:26:06.243',50,100,'S')
insert into yourtable values('AAA','2009-09-23 12:26:06.243',15,1010,'S')
insert into yourtable values('AAA','2009-09-24 12:26:06.243',25,500,'B')
insert into yourtable values('AAA','2009-09-25 12:26:06.243',35,700,'S')
insert into yourtable values('AAA','2009-09-25 12:26:06.243',5,700,'S')

here code 'AAA' 2 buys (100,25) and 4 sells (50,15,35,5)
Here for profit calculation the first three sell records needs to me mapped to the first buy (total buy qty = sum of 3 sellqty records) , the last sell needs to be mapped the second buy.
The sell buckets needs to mapped to the earliest availabe buy records.So the
buy records which are first in are mapped out first.

Thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources