ALTER PROCEDURE "dba"."pf_daily_holdg_detl_variance"(in @valuation_dt date,in @acct_grp_id integer,in @valuation_dt_dif date, in @subAcct_ID integer default null, @fi_securitySybmOrShortDescCriteria varchar(80) default null) //returns integer begin /* returns issues which are - now missing from this valuation date, - were not present in previous day (@valuation_dt_dif) 0r - have qty or book value changed or - rise beyond 85% of target price or - options or security which expires before next 10 days from now each row will have fi_sym, qty, book_value, market value ( of @valuation_dt if possible or @valuation_dt_dif) , difference in qty, book_value as of 20010514 addition as well as disposition shows up . 130621 added filter by fi_sym or fi_short_Desc using @fi_securitySybmOrShortDescCriteria @fi_securitySybmOrShortDescCriteria may has something like "fi_sym = 'value'", "fi_sym like '%value%'" -- with one or more % same goes for fi_short_desc. when fi_sym is not found fi_short_Desc is assumed @(#) still need resume . */ declare @idx int; declare @val varchar(70); if (@fi_securitySybmOrShortDescCriteria is null or len(@fi_securitySybmOrShortDescCriteria)=0) then select IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol, IfNull(a.qty,0-b.qty,a.qty-IsNull(b.qty,0)) as qty_chg, IfNull(a.lst_qot,-b.lst_qot,a.lst_qot) as last_qot, IfNull(a.cost,-b.cost,a.cost-IfNull(b.cost,0,b.cost)) as Book_val, IfNull(a.mkt_value,0-b.mkt_value,a.mkt_value-IfNull(b.mkt_value,0,b.mkt_value)) as MKt_val, a.lst_qot as qota,b.lst_qot as qotb, if(a.lst_qot is not null) and(b.lst_qot is not null) and b.lst_qot<>0 then (a.lst_qot-b.lst_qot)/b.lst_qot else 0.0 endif as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, IfNull(a.subAcct_ID,b.subAcct_ID,a.subAcct_ID) as subAcct_ID, IfNull(a.qty,b.qty,a.qty) as lst_qty, s.Exp_dt,s.target_price from pf_daily_holdg_detl as a right join pf_daily_holdg_detl as b on(a.fi_sym = b.fi_sym), fi_security as s where (a.valuation_dt=@valuation_dt) -- ---------------- a on @valuation_dt and(a.acct_grp_id=@acct_grp_id) and(a.subAcct_ID=b.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID) and(b.valuation_dt=@valuation_dt_dif) -- ---------------- b on @valuation_dt_dif and(b.acct_grp_id=@acct_grp_id) and(symbol=s.fi_sym) and(qty_chg<>0 or abs(mkt_variance)>=.1 or abs(target_pc)>.85 or datediff(day,current date,s.Exp_dt)<10) Union select a.fi_sym as symbol, a.qty as qty_chg, a.lst_qot as last_qot, a.cost as Book_val, a.mkt_value as MKt_val, a.lst_qot as qota,0 as qotb, 0.0 as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, a.subAcct_ID as subAcct_ID, /*b.qty*/0 as lst_qty, Exp_dt,s.target_price from pf_daily_holdg_detl as a,fi_security as s where (symbol=s.fi_sym) and(a.valuation_dt=@valuation_dt) -- ---------------- a on @valuation_dt and(a.acct_grp_id=@acct_grp_id) and not exists(select b.fi_sym from pf_daily_holdg_detl as b where (b.fi_sym=a.fi_sym) and(b.valuation_dt=@valuation_dt_dif) -- ---------------- b on @valuation_dt_dif and(b.acct_grp_id=@acct_grp_id) and(b.subAcct_ID=a.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID)); else set @idx=charindex('''',@fi_securitySybmOrShortDescCriteria); set @val=substr(@fi_securitySybmOrShortDescCriteria, @idx +1, len(@fi_securitySybmOrShortDescCriteria)-@idx -1); if (charIndex('fi_sym', fi_securitySybmOrShortDescCriteria) >=0) then if (charIndex('=',fi_securitySybmOrShortDescCriteria)>0) then select IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol, IfNull(a.qty,0-b.qty,a.qty-IsNull(b.qty,0)) as qty_chg, IfNull(a.lst_qot,-b.lst_qot,a.lst_qot) as last_qot, IfNull(a.cost,-b.cost,a.cost-IfNull(b.cost,0,b.cost)) as Book_val, IfNull(a.mkt_value,0-b.mkt_value,a.mkt_value-IfNull(b.mkt_value,0,b.mkt_value)) as MKt_val, a.lst_qot as qota,b.lst_qot as qotb, if(a.lst_qot is not null) and(b.lst_qot is not null) and b.lst_qot<>0 then (a.lst_qot-b.lst_qot)/b.lst_qot else 0.0 endif as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, IfNull(a.subAcct_ID,b.subAcct_ID,a.subAcct_ID) as subAcct_ID, IfNull(a.qty,b.qty,a.qty) as lst_qty, s.Exp_dt,s.target_price from pf_daily_holdg_detl as a,pf_daily_holdg_detl as b,fi_security as s where (a.fi_sym=*b.fi_sym) and(symbol=s.fi_sym) and a.fi_symb = @val and(a.valuation_dt=@valuation_dt) and(a.acct_grp_id=@acct_grp_id) and(a.subAcct_ID=b.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID) and(b.valuation_dt=@valuation_dt_dif) and(b.acct_grp_id=@acct_grp_id) and(qty_chg<>0 or abs(mkt_variance)>=.1 or abs(target_pc)>.85 or datediff(day,current date,s.Exp_dt)<10) union select a.fi_sym as symbol, a.qty as qty_chg, a.lst_qot as last_qot, a.cost as Book_val, a.mkt_value as MKt_val, a.lst_qot as qota,0 as qotb, 0.0 as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, a.subAcct_ID as subAcct_ID, 0 lst_qty, -- 130827 chgd as b.qty as lst_qty, Exp_dt,s.target_price from pf_daily_holdg_detl as a,fi_security as s where (symbol=s.fi_sym) and(a.valuation_dt=@valuation_dt) and(a.acct_grp_id=@acct_grp_id) and not exists(select b.fi_sym from pf_daily_holdg_detl as b where (b.fi_sym=a.fi_sym) and a.fi_symb = @val and(b.valuation_dt=@valuation_dt_dif) and(b.acct_grp_id=@acct_grp_id) and(b.subAcct_ID=a.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID)); else -- like not = fi_sym select IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol, IfNull(a.qty,0-b.qty,a.qty-IsNull(b.qty,0)) as qty_chg, IfNull(a.lst_qot,-b.lst_qot,a.lst_qot) as last_qot, IfNull(a.cost,-b.cost,a.cost-IfNull(b.cost,0,b.cost)) as Book_val, IfNull(a.mkt_value,0-b.mkt_value,a.mkt_value-IfNull(b.mkt_value,0,b.mkt_value)) as MKt_val, a.lst_qot as qota,b.lst_qot as qotb, if(a.lst_qot is not null) and(b.lst_qot is not null) and b.lst_qot<>0 then (a.lst_qot-b.lst_qot)/b.lst_qot else 0.0 endif as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, IfNull(a.subAcct_ID,b.subAcct_ID,a.subAcct_ID) as subAcct_ID, IfNull(a.qty,b.qty,a.qty) as lst_qty, s.Exp_dt,s.target_price from pf_daily_holdg_detl as a,pf_daily_holdg_detl as b,fi_security as s where (a.fi_sym=*b.fi_sym) and(symbol=s.fi_sym) and a.fi_symb = @val and(a.valuation_dt=@valuation_dt) and(a.acct_grp_id=@acct_grp_id) and(a.subAcct_ID=b.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID) and(b.valuation_dt=@valuation_dt_dif) and(b.acct_grp_id=@acct_grp_id) and(qty_chg<>0 or abs(mkt_variance)>=.1 or abs(target_pc)>.85 or datediff(day,current date,s.Exp_dt)<10) union select a.fi_sym as symbol, a.qty as qty_chg, a.lst_qot as last_qot, a.cost as Book_val, a.mkt_value as MKt_val, a.lst_qot as qota,0 as qotb, 0.0 as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, a.subAcct_ID as subAcct_ID, b.qty as lst_qty, Exp_dt,s.target_price from pf_daily_holdg_detl as a,fi_security as s where (symbol=s.fi_sym) and(a.valuation_dt=@valuation_dt) and(a.acct_grp_id=@acct_grp_id) and not exists(select b.fi_sym from pf_daily_holdg_detl as b where (b.fi_sym=a.fi_sym) and a.fi_symb = @val and(b.valuation_dt=@valuation_dt_dif) and(b.acct_grp_id=@acct_grp_id) and(b.subAcct_ID=a.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID)); end if Else -- not fi_sym so use short desc if (charIndex('=',@fi_securitySybmOrShortDescCriteria)>0) then select fi_sym into @val from dba.fi_security where fi_short_Desc = @val ; select IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol, IfNull(a.qty,0-b.qty,a.qty-IsNull(b.qty,0)) as qty_chg, IfNull(a.lst_qot,-b.lst_qot,a.lst_qot) as last_qot, IfNull(a.cost,-b.cost,a.cost-IfNull(b.cost,0,b.cost)) as Book_val, IfNull(a.mkt_value,0-b.mkt_value,a.mkt_value-IfNull(b.mkt_value,0,b.mkt_value)) as MKt_val, a.lst_qot as qota,b.lst_qot as qotb, if(a.lst_qot is not null) and(b.lst_qot is not null) and b.lst_qot<>0 then (a.lst_qot-b.lst_qot)/b.lst_qot else 0.0 endif as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, IfNull(a.subAcct_ID,b.subAcct_ID,a.subAcct_ID) as subAcct_ID, IfNull(a.qty,b.qty,a.qty) as lst_qty, s.Exp_dt,s.target_price from pf_daily_holdg_detl as a,pf_daily_holdg_detl as b,fi_security as s where (a.fi_sym=*b.fi_sym) and(symbol=s.fi_sym) and a.fi_symb = @val and(a.valuation_dt=@valuation_dt) and(a.acct_grp_id=@acct_grp_id) and(a.subAcct_ID=b.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID) and(b.valuation_dt=@valuation_dt_dif) and(b.acct_grp_id=@acct_grp_id) and(qty_chg<>0 or abs(mkt_variance)>=.1 or abs(target_pc)>.85 or datediff(day,current date,s.Exp_dt)<10) union select a.fi_sym as symbol, a.qty as qty_chg, a.lst_qot as last_qot, a.cost as Book_val, a.mkt_value as MKt_val, a.lst_qot as qota,0 as qotb, 0.0 as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, a.subAcct_ID as subAcct_ID, b.qty as lst_qty, Exp_dt,s.target_price from pf_daily_holdg_detl as a,fi_security as s where (symbol=s.fi_sym) and(a.valuation_dt=@valuation_dt) and(a.acct_grp_id=@acct_grp_id) and not exists(select b.fi_sym from pf_daily_holdg_detl as b where (b.fi_sym=a.fi_sym) and a.fi_symb = @val and(b.valuation_dt=@valuation_dt_dif) and(b.acct_grp_id=@acct_grp_id) and(b.subAcct_ID=a.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID)); else -- shortDesc like select first fi_sym into @val from dba.fi_security where fi_short_Desc like @val ; select IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol, IfNull(a.qty,0-b.qty,a.qty-IsNull(b.qty,0)) as qty_chg, IfNull(a.lst_qot,-b.lst_qot,a.lst_qot) as last_qot, IfNull(a.cost,-b.cost,a.cost-IfNull(b.cost,0,b.cost)) as Book_val, IfNull(a.mkt_value,0-b.mkt_value,a.mkt_value-IfNull(b.mkt_value,0,b.mkt_value)) as MKt_val, a.lst_qot as qota,b.lst_qot as qotb, if(a.lst_qot is not null) and(b.lst_qot is not null) and b.lst_qot<>0 then (a.lst_qot-b.lst_qot)/b.lst_qot else 0.0 endif as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, IfNull(a.subAcct_ID,b.subAcct_ID,a.subAcct_ID) as subAcct_ID, IfNull(a.qty,b.qty,a.qty) as lst_qty, s.Exp_dt,s.target_price from pf_daily_holdg_detl as a,pf_daily_holdg_detl as b,fi_security as s where (a.fi_sym=*b.fi_sym) and(symbol=s.fi_sym) and a.fi_symb = @val and(a.valuation_dt=@valuation_dt) and(a.acct_grp_id=@acct_grp_id) and(a.subAcct_ID=b.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID) and(b.valuation_dt=@valuation_dt_dif) and(b.acct_grp_id=@acct_grp_id) and(qty_chg<>0 or abs(mkt_variance)>=.1 or abs(target_pc)>.85 or datediff(day,current date,s.Exp_dt)<10) union select a.fi_sym as symbol, a.qty as qty_chg, a.lst_qot as last_qot, a.cost as Book_val, a.mkt_value as MKt_val, a.lst_qot as qota,0 as qotb, 0.0 as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, a.subAcct_ID as subAcct_ID, b.qty as lst_qty, Exp_dt,s.target_price from pf_daily_holdg_detl as a,fi_security as s where (symbol=s.fi_sym) and(a.valuation_dt=@valuation_dt) and(a.acct_grp_id=@acct_grp_id) and not exists(select b.fi_sym from pf_daily_holdg_detl as b where (b.fi_sym=a.fi_sym) and a.fi_symb = @val and(b.valuation_dt=@valuation_dt_dif) and(b.acct_grp_id=@acct_grp_id) and(b.subAcct_ID=a.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID)); end if end if end if end /* old code pre- 20010514 change select IfNull(a.fi_sym,b.fi_sym,a.fi_sym) as symbol, IfNull(a.qty,0-b.qty,a.qty-IsNull(b.qty,0)) as qty_chg, IfNull(a.lst_qot,-b.lst_qot,a.lst_qot) as last_qot, IfNull(a.cost,-b.cost,a.cost-IfNull(b.cost,0,b.cost)) as Book_val, IfNull(a.mkt_value,0-b.mkt_value,a.mkt_value-IfNull(b.mkt_value,0,b.mkt_value)) as MKt_val, a.lst_qot as qota,b.lst_qot as qotb, if(a.lst_qot is not null) and(b.lst_qot is not null) and b.lst_qot<>0 then (a.lst_qot-b.lst_qot)/b.lst_qot else 0.0 endif as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, IfNull(a.subAcct_ID,b.subAcct_ID,a.subAcct_ID) as subAcct_ID, IfNull(a.qty,b.qty,a.qty) as lst_qty, s.Exp_dt,s.target_price from pf_daily_holdg_detl as a,pf_daily_holdg_detl as b,fi_security as s where (a.fi_sym=*b.fi_sym) and(symbol=s.fi_sym) and(a.valuation_dt=@valuation_dt) and(a.acct_grp_id=@acct_grp_id) and(a.subAcct_ID=b.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID) and(b.valuation_dt=@valuation_dt_dif) and(b.acct_grp_id=@acct_grp_id) and(qty_chg<>0 or abs(mkt_variance)>=.1 or abs(target_pc)>.85 or datediff(day,current date,s.Exp_dt)<10) union select a.fi_sym as symbol, a.qty as qty_chg, a.lst_qot as last_qot, a.cost as Book_val, a.mkt_value as MKt_val, a.lst_qot as qota,0 as qotb, 0.0 as mkt_variance, if s.target_price<>0 then last_qot/s.target_price else 0.0 endif as target_pc, a.subAcct_ID as subAcct_ID, b.qty as lst_qty, Exp_dt,s.target_price from pf_daily_holdg_detl as a,fi_security as s where (symbol=s.fi_sym) and(a.valuation_dt=@valuation_dt) and(a.acct_grp_id=@acct_grp_id) and not exists(select b.fi_sym from pf_daily_holdg_detl as b where (b.fi_sym=a.fi_sym) and(b.valuation_dt=@valuation_dt_dif) and(b.acct_grp_id=@acct_grp_id) and(b.subAcct_ID=a.subAcct_ID) and (@subAcct_ID = null or @subAcct_ID = a.subAcct_ID)) */