My first post (beginner).. I hope it is clear enough and appreciate your taking the time to consider helping.
I have an existing sproc that takes a cart transaction from a table and inserts shared values of that transaction into another table called Transaction_PT. The sproc works fine, it takes the total sale value and attributes profit share of that value to participating members. Now I need to modify this sproc to also look up the retailer's "price version". "price version" is figured out by finding the retailer's version number (between 0 and 3) and then looking at the product's table, finding the matching product_id and the matching price version. The sproc 'knows' the retailer's ID (salon_id) and the product ID. I want to know how to add code to this sproc to find the appropriate price version money amount and insert it into Transactions_PT . I am new to SQL and have never used variables before so here is the sproc followed by my rough attempt at the code to add:
ALTER PROC sp_Tran_PT_Inse rt
AS
INSERT INTO Transaction_PT (transaction_id _fk , pass_through_am t , salon_id , st_id,
salon_pt_pct , SSR_id , SSR_pt_pct , SP_id,
SP_pt_pct , S_Mgr_id , S_Mgr_pt_pct , st_pt_pct,
RS_Mgr_ID , rs_mgr_pt_pct)
select tr.transaction_ id , tr.pass_through _amt, tr.salon_id , tr.stylist_ID,
sal.pt_pct_to_s alon, ch.ssr_id , ssr.pt_pct_to_s sr , ch.sp_id,
sp.pt_pct_to_sp , ch.s_mgr_id , sm.pt_pct_to_s_ mgr , sty.pt_pct_to_s tylist,
rsm.rs_mgr_id , rsm.pt_pct_to_r s_mgr
from transactions tr
join Salon sal
on sal.salon_id = tr.salon_id
join channels ch
on ch.salon_id = tr.salon_id
left join sub_sales_refer ral ssr
on ch.ssr_id = ssr.ssr_id
left join sales_person sp
on ch.sp_id = sp.sp_id
left join sales_mgr sm
on ch.s_mgr_id = sm.s_mgr_id
left join reg_sales_mgr rsm
on ch.rs_mgr_id = rsm.rs_mgr_id
left join stylist sty
on tr.stylist_id = sty.stylist_id
where tr.processed_fl ag = 0
--and check TS
--NEED to update processed flag
UPDATE Transactions
SET processed_flag = 1
WHERE transaction_id in (select transaction_id_ fk from transaction_pt
where calc_flag = 0)
exec sp_Tran_PT_Upda te
----------------------------------------------------------------------------------
declare @Version as varchar, @Price as money
set @Version = (Price_Version from Salon Where Salon_ID = Salon_ID)
if @Version = 1
Set @Price = (select Version_1 from Zen_Products_De scription Where Product_ID = Product_ID)
ELSE
if @Version = 2
Set @Price = (select Version_2 from Zen_Products_De scription Where Product_ID = Product_ID)
ELSE
if @Version = 3
Set @Price = (select Version_3 from Zen_Products_De scription Where Product_ID = Product_ID)
INSERT INTO Transaction_PT ...
Does this describe my problem well enough? Would anyone be willing to show me how to insert the price version into Transaction_PT using this sproc and a better version of my attempted code? Thanks much.
--Mat
I have an existing sproc that takes a cart transaction from a table and inserts shared values of that transaction into another table called Transaction_PT. The sproc works fine, it takes the total sale value and attributes profit share of that value to participating members. Now I need to modify this sproc to also look up the retailer's "price version". "price version" is figured out by finding the retailer's version number (between 0 and 3) and then looking at the product's table, finding the matching product_id and the matching price version. The sproc 'knows' the retailer's ID (salon_id) and the product ID. I want to know how to add code to this sproc to find the appropriate price version money amount and insert it into Transactions_PT . I am new to SQL and have never used variables before so here is the sproc followed by my rough attempt at the code to add:
ALTER PROC sp_Tran_PT_Inse rt
AS
INSERT INTO Transaction_PT (transaction_id _fk , pass_through_am t , salon_id , st_id,
salon_pt_pct , SSR_id , SSR_pt_pct , SP_id,
SP_pt_pct , S_Mgr_id , S_Mgr_pt_pct , st_pt_pct,
RS_Mgr_ID , rs_mgr_pt_pct)
select tr.transaction_ id , tr.pass_through _amt, tr.salon_id , tr.stylist_ID,
sal.pt_pct_to_s alon, ch.ssr_id , ssr.pt_pct_to_s sr , ch.sp_id,
sp.pt_pct_to_sp , ch.s_mgr_id , sm.pt_pct_to_s_ mgr , sty.pt_pct_to_s tylist,
rsm.rs_mgr_id , rsm.pt_pct_to_r s_mgr
from transactions tr
join Salon sal
on sal.salon_id = tr.salon_id
join channels ch
on ch.salon_id = tr.salon_id
left join sub_sales_refer ral ssr
on ch.ssr_id = ssr.ssr_id
left join sales_person sp
on ch.sp_id = sp.sp_id
left join sales_mgr sm
on ch.s_mgr_id = sm.s_mgr_id
left join reg_sales_mgr rsm
on ch.rs_mgr_id = rsm.rs_mgr_id
left join stylist sty
on tr.stylist_id = sty.stylist_id
where tr.processed_fl ag = 0
--and check TS
--NEED to update processed flag
UPDATE Transactions
SET processed_flag = 1
WHERE transaction_id in (select transaction_id_ fk from transaction_pt
where calc_flag = 0)
exec sp_Tran_PT_Upda te
----------------------------------------------------------------------------------
declare @Version as varchar, @Price as money
set @Version = (Price_Version from Salon Where Salon_ID = Salon_ID)
if @Version = 1
Set @Price = (select Version_1 from Zen_Products_De scription Where Product_ID = Product_ID)
ELSE
if @Version = 2
Set @Price = (select Version_2 from Zen_Products_De scription Where Product_ID = Product_ID)
ELSE
if @Version = 3
Set @Price = (select Version_3 from Zen_Products_De scription Where Product_ID = Product_ID)
INSERT INTO Transaction_PT ...
Does this describe my problem well enough? Would anyone be willing to show me how to insert the price version into Transaction_PT using this sproc and a better version of my attempted code? Thanks much.
--Mat