December 12, 2008 at 7:15 am
I have the following in the table:
idQTY_INPrice_INQTY_OutPrice_out
115000010.0115000010.01
21510515
325123518
425203525
545251530
Base on the above, I want to product the result below:
idQTY_INPrice_INQTY_OutPrice_out
115000010.0115000010.01
2510515
210101018
325122518
425202525
510251025
515251530
5202500
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