Convert cursor to select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convert cursor to select

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 21:21:18

[code]DECLARE STK_CURSOR2 CURSOR FAST_FORWARD FOR
SELECT COALESCE(t.total_price, 0), COALESCE(t.quantity, 0), COALESCE(t.qtt_out, 0), t.do_date, t.trx_type, CASE WHEN h.forex_rate IS NULL OR h.forex_rate = 0 then 1 ELSE h.forex_rate END forex_rate
FROM (
SELECT CASE WHEN trx_type = 'ADJ' and quantity > 0 and @st_adjascost='Y' then 'PDO'
WHEN trx_type = 'WHX' and quantity > 0 and @st_whxascost='Y' then 'PDO' else trx_type END trx_type,
trnx_ref, quantity, total_price, qtt_out, do_date
FROM st_trx t right join
(
SELECT st_code
FROM st_mast
WHERE @a = 1 and (st_code=@st_code)
UNION
SELECT st_code
FROM st_msuspend
WHERE @b = 1 and (st_code=@st_code)
)m on t.st_code=m.st_code
WHERE do_date <= @enddate
)t left join st_head h on t.trnx_ref=h.do_no and t.trx_type=h.trx_type
ORDER BY t.do_date

OPEN STK_CURSOR2

FETCH NEXT FROM STK_CURSOR2
INTO @total_price, @quantity, @qtt_out, @do_date, @trx_type, @forex_rate

WHILE @@FETCH_STATUS = 0
BEGIN
SET @bforward = @bforward + (CASE WHEN @do_date < @startdate then @quantity-@qtt_out else 0 END)
SET @qty_in = @qty_in + (CASE WHEN @do_date <= @enddate and @do_date >= @startdate then @quantity else 0 END)
SET @qty_out = @qty_out + (CASE WHEN @do_date < @enddate and @do_date >= @startdate then @qtt_out else 0 END)
SET @prev_balance = @balance
SET @balance = @balance + (CASE WHEN @do_date < @enddate then @quantity-@qtt_out else 0 END)

if @trx_type = 'PDO'
BEGIN
if ISNULL(NULLIF((@forex_rate * @totaL_price),0) / NULLIF(@quantity,0),0) <> 0
BEGIN
SET @mrcost = (@forex_rate * @totaL_price) / @quantity
END

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
if @wacost = 0 BEGIN SET @wacost = @aver_cost END
END

FETCH NEXT FROM STK_CURSOR2
INTO @total_price, @quantity, @qtt_out, @do_date, @trx_type, @forex_rate

END
CLOSE STK_CURSOR2
DEALLOCATE STK_CURSOR2[/code]
hi, guys...how to convert this to 1 select statement....i deadlock at the top mrcost cost <> 0 there....
[edit]show where i need help...

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 22:52:08

i think i stay back to cursor...seems to be too complicated..

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 23:14:53

what is the cursor doing ? I don't see anything beside calculating some variables. Is this the complete query ?

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 23:42:14

ya...this cursor is to calculate data.
i need to get
@bforward = SUM(CASE WHEN do_date < @startdate then quantity-qtt_out else 0 END)
@qty_in = SUM(CASE WHEN do_date <= @enddate and do_date >= @startdate then quantity else 0 END)
@qty_out = SUM(CASE WHEN do_date < @enddate and do_date >= @startdate then qtt_out else 0 END)
@balance = SUM(CASE WHEN do_date < @enddate then quantity-qtt_out else 0 END)
@mrcost = ((@forex_rate * @totaL_price) / @quantity) with condition TOP 1 do_date, ((forex_rate * totaL_price) / quantity)<> 0, trx_type = 'PDO'
@wacost = i also dunno how to explain....just try to match above formula...
from

quote:

SELECT	COALESCE(t.total_price, 0), COALESCE(t.quantity, 0), COALESCE(t.qtt_out, 0), t.do_date, t.trx_type, CASE WHEN h.forex_rate IS NULL OR h.forex_rate = 0 then 1 ELSE h.forex_rate END forex_rate
FROM	(
	SELECT	CASE WHEN trx_type = 'ADJ' and quantity > 0 and @st_adjascost='Y' then 'PDO'
		WHEN trx_type = 'WHX' and quantity > 0 and @st_whxascost='Y' then 'PDO' else trx_type END trx_type,
		trnx_ref, quantity, total_price, qtt_out, do_date
	FROM	st_trx t right join 
			(
			SELECT	st_code
			FROM	st_mast
			WHERE	@a = 1 and (st_code=@st_code)
			UNION
			SELECT	st_code
			FROM	st_msuspend
			WHERE	@b = 1 and (st_code=@st_code)
			)m on t.st_code=m.st_code
	WHERE	do_date <= @enddate
	)t left join st_head h on t.trnx_ref=h.do_no and t.trx_type=h.trx_type
ORDER BY t.do_date

red = i solved....

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources