regarding cursors and loops - 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 2000 Forums

SQL Server Development (2000)

regarding cursors and loops

Author

Topic

Bhumika123
Starting Member

1 Post

Posted - 2012-05-16 : 02:35:38

hello guys...
i need your help in sqlserver 2000 programming plz guide me regrading cursors and loops..
I need to write a procedure in sql server 2000 to roundoff the cheaper order values and the condition is as follows:
if p+q+r > 15
if p+q+r < 7 then
update table
set p= 0,q = 0,n=0 p,q,n are size_codes and there values are stored in qty , final_qty , supplied_qty. value of qty should be same and values of final_qty and supplied_qty should be changed as per roundoff conditions.

if p+q+r >= 7 then
rem_qty = 15 - (p+q+r)
now rem_qty will be added in either p,q or r which is greater in all three

i have put my efforts do it but i am getting problems my procedure doesnot roundoff table values as per roundoff conditions..

i am posting my stored procedure.... but plz help me in correcting it.

CREATE Procedure usp_CheaperRoundoff --updated by bhumika 1542012

@order_id numeric,
@excise_order_no numeric,
@order_dt datetime,
@period_from_dt datetime,
@period_to_dt datetime,
@order_type varchar(10),
@ctg varchar(10),
@msg varchar(50) =null output,
@adt_user_id varchar(20)

AS

declare
@FinalPurchase_amt numeric(15,3),
@ln_purchase_amt numeric(18,3),
@ln_bottle_per_case numeric(18),
@recovery_amt numeric(15,3),
@ln_tot_stax numeric(15,3),
@ln_tot_sd_fee numeric(15,3),
@ln_tot_ast_fee numeric(15,3),
@ln_tot_vnd_fee numeric(15,3),
@ln_tot_sale_price numeric(15,3),
@ln_tot_final_stax numeric(15,3),
@ln_tot_final_sd_fee numeric(15,3),
@ln_tot_final_ast_fee numeric(15,3),
@ln_tot_final_vnd_fee numeric(15,3),
@ln_tot_final_sale_price numeric(15,3),
--@order_id numeric(18,0),
@ld_period_from_dt datetime,
@ld_period_to_dt datetime,
@ln_order_counter numeric,
@cntr numeric,
@ln_collection_count numeric,
@ln_brand_collection_price numeric(15,3),
@licencee_code varchar(10),
@shop_code varchar(10),
@brand_code varchar(10),
@size_code varchar(10),
@adt_trans_time datetime,
--@lv_adt_user_id varchar(20),
@ln_ord_qty numeric(18,0),
@qty numeric(18,0),
@rem_ord_qty numeric(18,0),
@final_qty numeric(18,0),
@supplied_qty numeric(18,0),
@N numeric(18,0),
@Q numeric(18,0),
@P numeric(18,0),
@sum_qty numeric(18,0),
@ln_min_ord_qty numeric(15,3),
@ln_cl_bal numeric,
--@order_dt datetime,
--@ctg varchar(10)
@period_id numeric(18,0)
--@lv_popular varchar(1)

DECLARE cur1 CURSOR FOR SELECT period_from_dt, period_to_dt,shop_code,
brand_code, size_code,ctg, cl_bal, brand_collection_price,
adt_trans_time, adt_user_id FROM ims_stock_tracking_trans_dat
WHERE ctg='2' and period_id in (select period_id from ims_order_dat where order_id = '32829')
--AND shop_code='AMAP-CL' and brand_code in ('CHAO')
--and size_code='Q'

SET @ln_order_counter = 0

SELECT @ln_collection_count = count(shop_code)
FROM IMS_PERIOD_COLLECTION_DAT
WHERE period_id in ( select period_id from ims_order_dat where order_id = order_id and period_from_dt = @ld_period_from_dt)
AND complete_flag='N'

IF (@ln_collection_count >0)
BEGIN
SET @msg='Order is already created and is ready for roundoff'
return
END

OPEN cur1

FETCH next from cur1 INTO @ld_period_from_dt,@ld_period_to_dt,@shop_code,
@brand_code,@size_code,@ctg,@ln_cl_bal,@ln_brand_collection_price,
@adt_trans_time,@adt_user_id

WHILE @@FETCH_STATUS = 0
BEGIN

/*FETCH next from cur1 INTO @ld_period_from_dt,@ld_period_to_dt,@shop_code,
@brand_code,@size_code,@ctg,@ln_cl_bal,@ln_brand_collection_price,
@adt_trans_time,@adt_user_id */

DECLARE cur2 CURSOR FOR SELECT order_id,shop_code,brand_code,licencee_code,size_code,qty,final_qty,supplied_qty,purchase_amt,recovery_amt,tot_stax,
tot_sd_fee,tot_ast_fee,tot_vnd_fee,tot_sale_price,adt_trans_time,adt_user_id
from ims_order_detail_dat where order_id in (select order_id from ims_order_dat where excise_order_no = @excise_order_no and ctg = @ctg and period_id = @period_id)
and final_qty > 0 and supplied_qty <> 0 and supplied_qty = final_qty

select
@order_dt = order_dt
from ims_order_dat
where excise_order_no = @excise_order_no

set @cntr = 0

OPEN cur2

FETCH next from cur2 INTO @order_id,@shop_code,@brand_code,
@licencee_code,@size_code,@qty,@final_qty,@supplied_qty,@ln_purchase_amt,@recovery_amt,@ln_tot_stax,@ln_tot_sd_fee,@ln_tot_ast_fee,@ln_tot_vnd_fee,@ln_tot_sale_price,@adt_trans_time,@adt_user_id

WHILE (@cntr > 0) AND @@FETCH_STATUS = 0
BEGIN
SET @cntr = @cntr + 1

/*FETCH next from cur2 INTO @order_id,@shop_code,@brand_code,
@licencee_code,@size_code,@qty,@final_qty,@supplied_qty,@ln_purchase_amt,@recovery_amt,@ln_tot_stax,@ln_tot_sd_fee,@ln_tot_ast_fee,@ln_tot_vnd_fee,@ln_tot_sale_price,@adt_trans_time,@adt_user_id*/

/*SELECT
popular,licencee_code
FROM IMS_BRAND_TAB
WHERE brand_code=@brand_code*/

SELECT
@ln_purchase_amt = ws_price,
@ln_tot_stax = stax,
@ln_tot_sd_fee = sd_fee,
@ln_tot_ast_fee = ast_fee,
@ln_tot_vnd_fee = vnd_fee,
@ln_tot_sale_price = sale_price,
@ln_bottle_per_case = bottle_per_case
FROM IMS_BRAND_DETAIL_TAB
WHERE brand_code=@brand_code
AND size_code=@size_code
AND brand_price_eff_dt in (SELECT max(brand_price_eff_dt)
FROM IMS_BRAND_DETAIL_TAB
WHERE brand_code=@brand_code
AND size_code= @size_code)

--SET @ln_order_counter = @ln_order_counter + 1

IF(@ctg in ('2')) /// roundoff logic....................
BEGIN

SET @ln_min_ord_qty = 15

/*FETCH next from cur2 INTO @order_id,@shop_code,@brand_code,
@licencee_code,@size_code,@qty,@final_qty,@supplied_qty,@ln_purchase_amt,@recovery_amt,@ln_tot_stax,@ln_tot_sd_fee,@ln_tot_ast_fee,@ln_tot_vnd_fee,@ln_tot_sale_price,@adt_trans_time,@adt_user_id*/

select @N = qty from ims_order_detail_dat
where order_id = @order_id
and shop_code = @shop_code
and brand_code = @brand_code
and size_code = 'N'

select @P = qty from ims_order_detail_dat
where order_id = @order_id
and shop_code = @shop_code
and brand_code = @brand_code
and size_code = 'P'

select @Q = qty from ims_order_detail_dat
where order_id = @order_id
and shop_code = @shop_code
and brand_code = @brand_code
and size_code = 'Q'

SET @sum_qty = @N + @P + @Q

IF (@sum_qty < @ln_min_ord_qty)
BEGIN
IF (@sum_qty < 7)
BEGIN
SET @final_qty = 0
SET @FinalPurchase_amt = @final_qty*@ln_purchase_amt*@ln_bottle_per_case
SET @ln_tot_final_stax = @final_qty*@ln_tot_stax*@ln_bottle_per_case
SET @ln_tot_final_sd_fee = @final_qty*@ln_tot_sd_fee*@ln_bottle_per_case
SET @ln_tot_final_ast_fee = @final_qty*@ln_tot_ast_fee*@ln_bottle_per_case
SET @ln_tot_final_vnd_fee = @final_qty*@ln_tot_vnd_fee*@ln_bottle_per_case
SET @ln_tot_final_sale_price = @final_qty*@ln_tot_sale_price*@ln_bottle_per_case

update ims_order_detail_dat
set
final_qty = @final_qty ,
supplied_qty = @final_qty,
purchase_amt = @FinalPurchase_amt,
tot_stax = @ln_tot_final_stax,
tot_sd_fee = @ln_tot_final_sd_fee,
tot_ast_fee = @ln_tot_final_ast_fee,
tot_vnd_fee = @ln_tot_final_vnd_fee,
tot_sale_price = @ln_tot_final_sale_price,
adt_user_id = @adt_user_id,
adt_trans_time =getdate()
where
order_id = @order_id and
shop_code = @shop_code and
brand_code = @brand_code and
size_code IN ('N','P','Q')
END

ELSE
BEGIN
SET @rem_ord_qty = 15 - @sum_qty
IF(@P > @Q) AND (@P > @N)
BEGIN
SET @final_qty = @P + @rem_ord_qty
SET @FinalPurchase_amt = @final_qty*@ln_purchase_amt*@ln_bottle_per_case
SET @ln_tot_final_stax = @final_qty*@ln_tot_stax*@ln_bottle_per_case
SET @ln_tot_final_sd_fee = @final_qty*@ln_tot_sd_fee*@ln_bottle_per_case
SET @ln_tot_final_ast_fee = @final_qty*@ln_tot_ast_fee*@ln_bottle_per_case
SET @ln_tot_final_vnd_fee = @final_qty*@ln_tot_vnd_fee*@ln_bottle_per_case
SET @ln_tot_final_sale_price = @final_qty*@ln_tot_sale_price*@ln_bottle_per_case
update ims_order_detail_dat
set
final_qty = @final_qty,
supplied_qty = @final_qty,
purchase_amt = @FinalPurchase_amt,
tot_stax = @ln_tot_final_stax,
tot_sd_fee = @ln_tot_final_sd_fee,
tot_ast_fee = @ln_tot_final_ast_fee,
tot_vnd_fee = @ln_tot_final_vnd_fee,
tot_sale_price = @ln_tot_final_sale_price,
adt_user_id = @adt_user_id,
adt_trans_time = getdate()
where
order_id = @order_id and
shop_code = @shop_code and
brand_code = @brand_code and
size_code = 'P'
END
ELSE
BEGIN
IF(@Q > @P) AND (@Q > @N)
BEGIN
SET @final_qty = @Q + @rem_ord_qty
SET @FinalPurchase_amt = @final_qty*@ln_purchase_amt*@ln_bottle_per_case
SET @ln_tot_final_stax = @final_qty*@ln_tot_stax*@ln_bottle_per_case
SET @ln_tot_final_sd_fee = @final_qty*@ln_tot_sd_fee*@ln_bottle_per_case
SET @ln_tot_final_ast_fee = @final_qty*@ln_tot_ast_fee*@ln_bottle_per_case
SET @ln_tot_final_vnd_fee = @final_qty*@ln_tot_vnd_fee*@ln_bottle_per_case
SET @ln_tot_final_sale_price = @final_qty*@ln_tot_sale_price*@ln_bottle_per_case
update ims_order_detail_dat
set
final_qty = @final_qty,
supplied_qty =@final_qty,
purchase_amt =@FinalPurchase_amt,
tot_stax=@ln_tot_final_stax,
tot_sd_fee=@ln_tot_final_sd_fee,
tot_ast_fee=@ln_tot_final_ast_fee,
tot_vnd_fee=@ln_tot_final_vnd_fee,
tot_sale_price=@ln_tot_final_sale_price,
adt_user_id =@adt_user_id,
adt_trans_time =getdate()
where
order_id =@order_id and
shop_code =@shop_code and
brand_code =@brand_code and
size_code = 'Q'

END
ELSE
BEGIN
SET @final_qty = @N + @rem_ord_qty
SET @FinalPurchase_amt = @final_qty*@ln_purchase_amt*@ln_bottle_per_case
SET @ln_tot_final_stax = @final_qty*@ln_tot_stax*@ln_bottle_per_case
SET @ln_tot_final_sd_fee = @final_qty*@ln_tot_sd_fee*@ln_bottle_per_case
SET @ln_tot_final_ast_fee = @final_qty*@ln_tot_ast_fee*@ln_bottle_per_case
SET @ln_tot_final_vnd_fee = @final_qty*@ln_tot_vnd_fee*@ln_bottle_per_case
SET @ln_tot_final_sale_price = @final_qty*@ln_tot_sale_price*@ln_bottle_per_case
update ims_order_detail_dat
set
final_qty = @final_qty,
supplied_qty =@final_qty,
purchase_amt =@FinalPurchase_amt,
tot_stax=@ln_tot_final_stax,
tot_sd_fee=@ln_tot_final_sd_fee,
tot_ast_fee=@ln_tot_final_ast_fee,
tot_vnd_fee=@ln_tot_final_vnd_fee,
tot_sale_price=@ln_tot_final_sale_price,
adt_user_id =@adt_user_id,
adt_trans_time =getdate()
where
order_id =@order_id and
shop_code =@shop_code and
brand_code =@brand_code and
size_code = 'N'
END
-- END
END
END
/*if((@ln_cl_bal=0) AND (@ln_ord_qty=0))
BEGIN
SET @ln_ord_qty=15
END*/
END
ELSE
IF (@sum_qty > @ln_min_ord_qty)
BEGIN
RETURN
END
END

if((@ln_cl_bal=0) AND (@ln_ord_qty=0)) /// roundoff policy
BEGIN
SET @ln_ord_qty=15
END
--END

/*FETCH next from cur1 INTO @ld_period_from_dt,@ld_period_to_dt,@shop_code,
@brand_code,@size_code,@ctg,@ln_cl_bal,@ln_brand_collection_price,
@ld_adt_trans_time, @lv_adt_user_id */

FETCH next from cur2 INTO @order_id,@shop_code,@brand_code,@licencee_code,
@size_code,@qty,@final_qty,@supplied_qty,@ln_purchase_amt,@recovery_amt,@ln_tot_stax,@ln_tot_sd_fee,
@ln_tot_ast_fee,@ln_tot_vnd_fee,@ln_tot_sale_price,@adt_trans_time,@adt_user_id

END
CLOSE cur2
DEALLOCATE cur2

FETCH next from cur1 INTO @ld_period_from_dt,@ld_period_to_dt,@shop_code,
@brand_code,@size_code,@ctg,@ln_cl_bal,@ln_brand_collection_price,
@adt_trans_time, @adt_user_id

END
CLOSE cur1
DEALLOCATE cur1

--exec usp_CheaperRoundoff '32829','2','2011-12-25','2011-12-14','2011-12-21','G','2','','bhumika123'

--select * from ims_order_detail_dat where order_id = '32829' and final_qty > 0

---select * from ims_order_dat where order_id = '32829'

GO

plz check where the logic is wrong and do reply.....

bhumika

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-16 : 09:46:39

I didn't really understand but you don't need a cursor for it. Something like this can do it in 2 queries.:

I DIDN'T UNDERSTAND YOUR LOGIC but hopefully you can see what I'm trying to say.



UPDATE <theTable> SET
	[p] = 0
	, [q] = 0
	, [n] = 0
WHERE
	[p] + [q] + [r] < 7
	
UPDATE <theTable> SET
	[p] = CASE WHEN [p] > [q] AND [p] > [r] THEN 15 - ([p] + [q] + [r]) ELSE [p] END
	, [q] = CASE WHEN [q] > [p] AND [q] > [r] THEN 15 - ([p] + [q] + [r]) ELSE [p] END
	, [r] = CASE WHEN [r] > [p] AND [r] > [q] THEN 15 - ([p] + [q] + [r]) ELSE [p] END
WHERE
	[p] + [q] + [r] >= 7

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources