query result....

  • 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

  • i dont know if other can understand your requirement but at least i dont understand what you trying to achieve?

  • 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