December 12, 2008 at 7:15 am
I have the following in the table:
id QTY_IN Price_IN QTY_Out Price_out
1 150000 10.01 150000 10.01
2 15 10 5 15
3 25 12 35 18
4 25 20 35 25
5 45 25 15 30
Base on the above, I want to product the result below:
id QTY_IN Price_IN QTY_Out Price_out
1 150000 10.01 150000 10.01
2 5 10 5 15
2 10 10 10 18
3 25 12 25 18
4 25 20 25 25
5 10 25 10 25
5 15 25 15 30
5 20 25 0 0
any suggestion on how to solve is greatly appreciates.
thank
December 12, 2008 at 7:26 am
i dont know if other can understand your requirement but at least i dont understand what you trying to achieve?
December 12, 2008 at 7:37 am
Hi dva2007,
this is the track of the stock which has come first at what cost and stock which is going out at what price base on FIFO (First in First Out). The code below return the result, however it is taking a bit to long due to the use of temp table. Any suggestion on how to improve or re-write this stuffs.
thanks
--table UTIL_NUMS is a number base from 1 to 5,000,000
select 1 id,150000 as qty_in, 10.01 price_in, 150000 qty_out, 10.01 price_out
into #tblTest
union all
select 2, 15, 10, 5, 15 union all
select 3, 25, 12, 35, 18 union all
select 4, 25, 20, 35, 25 union all
select 5, 45, 25, 15, 30
select * from #tblTest
SELECT IDENTITY(INT, 0, 1) AS RowID,
s.ID,
s.Price_in
INTO #Bx
FROM #tblTest AS s
inner join UTIL_NUMS AS V
ON V.TYPE = 'P'
WHERE v.N >= 1
AND v.N <= s.Qty_in
ORDER BY s.id
SELECT IDENTITY(INT, 0, 1) AS RowID,
s.ID,
s.Price_out
INTO #Sx
FROM #tblTest AS s
inner join UTIL_NUMS AS V
ON V.TYPE = 'P'
WHERE v.N >= 1
AND v.N <= s.Qty_out
ORDER BY s.id
SELECT id,Qty_in,Cost,Qty_out,
Sale
FROM (
SELECT MIN(b.RowID) AS RowID,
b.id,
COUNT(b.id) AS Qty_in,
MIN(b.Price_in) AS Cost,
COUNT(s.id) AS Qty_out,
MIN(s.Price_out) AS Sale
FROM #Bx AS b
full JOIN #Sx AS s ON s.RowID = b.RowID
GROUP BY b.ID,
s.ID
) AS d
ORDER BY RowID
thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply