quote:

---

if @prev_balance >= 0
BEGIN
if @wacost = 0 BEGIN SET @wacost = @mrcost END
else BEGIN SET @wacost = ISNULL(NULLIF((@prev_balance*@wacost+@quantity*@mrcost), 0)/NULLIF((@prev_balance+@quantity), 0), 0) END
END
else BEGIN SET @wacost = @mrcost END

---

;with cte (trx_type, trnx_ref, quantity, qtt_out, total_price, forex_rate, [name], do_date, mrcost, row_no)
as
(
	SELECT	t.trx_type, 
		t.trnx_ref, 
		quantity	= COALESCE(t.quantity, 0), 
		qtt_out		= COALESCE(t.qtt_out, 0), 
		total_price	= COALESCE(t.total_price, 0), 
		forex_rate	= CASE WHEN h.forex_rate IS NULL or h.forex_rate = 0 then 1 ELSE h.forex_rate END, 
		[name]		= CASE WHEN t.trx_type IN ('PDO','GRO') then name else cm_name END, 
		t.do_date,
		mrcost 		= ISNULL(NULLIF((forex_rate * totaL_price), 0) / NULLIF(quantity, 0), 0),
		row_no		= ROW_NUMBER() OVER (ORDER BY t.trx_type, t.trnx_ref, to_date)
	FROM	(
			SELECT	CASE 	WHEN 	trx_type = 'ADJ' 
					and 	quantity > 0 
					and 	(SELECT content a FROM sys_config WHERE header = 'ST_ADJASCOST') = 'Y' 
					then 	'PDO'
					WHEN 	trx_type = 'WHX' 
					and 	quantity > 0 
					and 	(SELECT content a FROM sys_config WHERE header = 'ST_WHXASCOST') = 'Y' 
					then 	'PDO' 
					else 	trx_type 
					END 	trx_type,
				t.trnx_ref, 
				quantity, 
				qtt_out, 
				total_price, 
				[name], 
				cm_name, 
				do_date 
			FROM	st_trx t 
				left join customer c on t.company=c.cm_cust_no
				left join supplier s on t.company=s.supp_no
			WHERE	do_date <= '20091231'
			and 	st_code	= @st_code
		) t 
		left join st_head h 	on 	t.trx_type	= h.trx_type 
					and 	t.trnx_ref	= h.do_no
)
select	*
into	#temp
from	cte c
	cross apply
	(
		select	balance 	= SUM(CASE WHEN do_date <= @enddate then quantity - qtt_out else 0 END),
		from	cte x
		where	x.row_no	<= c.row_no
	) d

select	t1.*,
	wacost	= case 	when t2.balance	>= 0	and  	t2.wacost	= 0
			then 
				t1.mrcost
			else 
				ISNULL(NULLIF((t2.balance * t2.wacost + t1.quantity * t1.mrcost), 0)
			/	NULLIF((t2.balance + t1.quantity), 0), 0)
			end
from	#temp t1
	left join #temp t2	on	t1.row_no	= t2.row_no + 1