query result for Profit/loss analysis

[tran008]

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

 

[tran008]

Base on the code below, this is how I came up with the result, however this involve with temp table...which taking a bit to return the resolved. Anyone have any other suggestions on how to do this.

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