select select bumeng.departname,diqu.str_crm_dir,diqu.str_dir_name, isnull(zj.xse,0) as zj_xse,zj.ml as zj_ml ,zj.mll*100 as zj_mll, cgdb.xse as cgdb_xse,cgdb.ml as cgdb_ml ,cgdb.mll*100 as cgdb_mll, yxzx.xse as yxzx_xse,yxzx.ml as yxzx_ml ,yxzx.mll*100 as yxzx_mll, ylqxzy.xse as ylqxzy_xse,ylqxzy.ml as ylqxzy_ml,ylqxzy.mll*100 as ylqxzy_mll, ylqxxs.xse as ylqxxs_xse,ylqxxs.ml as ylqxxs_ml,ylqxxs.mll*100 as ylqxxs_mll, xpyb.xse as xpyb_xse,xpyb.ml as xpyb_ml,xpyb.mll*100 as xpyb_mll, xpeb.xse as xpeb_xse,xpeb.ml as xpeb_ml,xpeb.mll*100 as xpeb_mll, xpsb.xse as xpsb_xse,xpsb.ml as xpsb_ml,xpsb.mll*100 as xpsb_mll, cpkfb.xse as cpkfb_xse,cpkfb.ml as cpkfb_ml,cpkfb.mll*100 as cpkfb_mll, zyb.xse as zyb_xse,zyb.ml as zyb_ml,zyb.mll*100 as zyb_mll, yydlb.xse as yydlb_xse,yydlb.ml as yydlb_ml,yydlb.mll*100 as yydlb_mll, bjpb.xse as bjpb_xse,bjpb.ml as bjpb_ml,bjpb.mll*100 as bjpb_mll, cpyybyyzx.xse as cpyybyyzx_xse,cpyybyyzx.ml as cpyybyyzx_ml,cpyybyyzx.mll*100 as cpyybyyzx_mll, cpyybyxzxxs.xse as cpyybyxzxxs_xse,cpyybyxzxxs.ml as cpyybyxzxxs_ml,cpyybyxzxxs.mll*100 as cpyybyxzxxs_mll, myylk.xse as myylk_xse,myylk.ml as myylk_ml,myylk.mll*100 as myylk_mll, yyzyb.xse as yyzyb_xse,yyzyb.ml as yyzyb_ml,yyzyb.mll*100 as yyzyb_mllfrom [dyyunfei].[dbo].t_depart bumeng (NoLock) inner join[dyyunfei].[dbo].t_CRM_DIR gxi(NoLock) on bumeng.departcode=gxi.departcode inner join t_crm_dir diqu (NoLock) on gxi.str_crm_dir=diqu.str_crm_dir left outer join--总计 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') group by a.str_crm_dir,a.str_dir_name )zj on zj.str_crm_dir = diqu.str_crm_dirleft outer join--采购调拨 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.n_sellman_id=1390 and c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') group by a.str_crm_dir,a.str_dir_name )cgdb on ( cgdb.str_crm_dir = diqu.str_crm_dir)left outer join--营销中心 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.01%' group by a.str_crm_dir,a.str_dir_name )yxzx on (yxzx.str_crm_dir = diqu.str_crm_dir) left outer join--医疗器械自营 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.02%' and c.n_sellman_id=1375group by a.str_crm_dir,a.str_dir_name )ylqxzy on (ylqxzy.str_crm_dir = diqu.str_crm_dir) left outer join--医疗器械销售 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.02%' and c.n_sellman_id!=1375group by a.str_crm_dir,a.str_dir_name )ylqxxs on (ylqxxs.str_crm_dir = diqu.str_crm_dir)
left outer join--新品一部 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.03%'group by a.str_crm_dir,a.str_dir_name )xpyb on (xpyb.str_crm_dir = diqu.str_crm_dir) left outer join (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.04%'group by a.str_crm_dir,a.str_dir_name )xpsb on (xpsb.str_crm_dir = diqu.str_crm_dir) left outer join-- 产品开发部 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.05%'
group by a.str_crm_dir,a.str_dir_name )cpkfb on (cpkfb.str_crm_dir = diqu.str_crm_dir) left outer join--中药部 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.06%'group by a.str_crm_dir,a.str_dir_name )zyb on (zyb.str_crm_dir = diqu.str_crm_dir) left outer join--医院代理部 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.07%'group by a.str_crm_dir,a.str_dir_name )yydlb on (yydlb.str_crm_dir = diqu.str_crm_dir) left outer join--保健品部 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.08%'group by a.str_crm_dir,a.str_dir_name )bjpb on (bjpb.str_crm_dir = diqu.str_crm_dir) left outer join--产品营运部营运自营 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.09%' and c.n_sellman_id=1374group by a.str_crm_dir,a.str_dir_name )cpyybyyzx on (cpyybyyzx.str_crm_dir = diqu.str_crm_dir) left outer join--产品营运部营销中心销售 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.09%' and c.n_sellman_id!=1374group by a.str_crm_dir,a.str_dir_name )cpyybyxzxxs on (cpyybyxzxxs.str_crm_dir = diqu.str_crm_dir) left outer join--新品二部 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.10%'group by a.str_crm_dir,a.str_dir_name )xpeb on (xpeb.str_crm_dir = diqu.str_crm_dir) left outer join --麻药部 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.11%'group by a.str_crm_dir,a.str_dir_name )myylk on (myylk.str_crm_dir = diqu.str_crm_dir) left outer join--医院部自营 (select a.str_crm_dir,a.str_dir_name as str_dir_name, sum(-d.d_money) as xse, sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml, (sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mll
from v_account_stockintrade c (NoLock) inner join v_account_stockintrade_detail d (NoLock) on c.n_stockaccount_id=d.n_stockaccount_id inner join t_account_dir e (NoLock) on e.n_account_id=d.n_account_id inner join t_crm_customers b (NoLock) on b.n_crm_id=c.n_crm_id inner join t_CRM_DIR a (NoLock) on left(b.str_crm_dir,11)=a.str_crm_dir where c.dt_chalkup between @dateup and @dateend and a.str_crm_dir like '__.__.__.__%' and c.str_operate_code in('006','106','606') and e.str_depart_coding like'00.01.12%'group by a.str_crm_dir,a.str_dir_name )yyzyb on (yyzyb.str_crm_dir = diqu.str_crm_dir)order by gxi.departcode,diqu.str_crm_dir
select bumeng.departname,diqu.str_crm_dir,diqu.str_dir_name,
isnull(zj.xse,0) as zj_xse,zj.ml as zj_ml ,zj.mll*100 as zj_mll,
cgdb.xse as cgdb_xse,cgdb.ml as cgdb_ml ,cgdb.mll*100 as cgdb_mll,
yxzx.xse as yxzx_xse,yxzx.ml as yxzx_ml ,yxzx.mll*100 as yxzx_mll,
ylqxzy.xse as ylqxzy_xse,ylqxzy.ml as ylqxzy_ml,ylqxzy.mll*100 as ylqxzy_mll,
ylqxxs.xse as ylqxxs_xse,ylqxxs.ml as ylqxxs_ml,ylqxxs.mll*100 as ylqxxs_mll,
xpyb.xse as xpyb_xse,xpyb.ml as xpyb_ml,xpyb.mll*100 as xpyb_mll,
xpeb.xse as xpeb_xse,xpeb.ml as xpeb_ml,xpeb.mll*100 as xpeb_mll,
xpsb.xse as xpsb_xse,xpsb.ml as xpsb_ml,xpsb.mll*100 as xpsb_mll,
cpkfb.xse as cpkfb_xse,cpkfb.ml as cpkfb_ml,cpkfb.mll*100 as cpkfb_mll,
zyb.xse as zyb_xse,zyb.ml as zyb_ml,zyb.mll*100 as zyb_mll,
yydlb.xse as yydlb_xse,yydlb.ml as yydlb_ml,yydlb.mll*100 as yydlb_mll,
bjpb.xse as bjpb_xse,bjpb.ml as bjpb_ml,bjpb.mll*100 as bjpb_mll,
cpyybyyzx.xse as cpyybyyzx_xse,cpyybyyzx.ml as cpyybyyzx_ml,cpyybyyzx.mll*100 as cpyybyyzx_mll,
cpyybyxzxxs.xse as cpyybyxzxxs_xse,cpyybyxzxxs.ml as cpyybyxzxxs_ml,cpyybyxzxxs.mll*100 as cpyybyxzxxs_mll,
myylk.xse as myylk_xse,myylk.ml as myylk_ml,myylk.mll*100 as myylk_mll,
yyzyb.xse as yyzyb_xse,yyzyb.ml as yyzyb_ml,yyzyb.mll*100 as yyzyb_mllfrom [dyyunfei].[dbo].t_depart bumeng (NoLock)
inner join[dyyunfei].[dbo].t_CRM_DIR gxi(NoLock)
on bumeng.departcode=gxi.departcode
inner join t_crm_dir diqu (NoLock)
on gxi.str_crm_dir=diqu.str_crm_dir
left outer join--总计
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
group by a.str_crm_dir,a.str_dir_name )zj
on zj.str_crm_dir = diqu.str_crm_dirleft outer join--采购调拨
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.n_sellman_id=1390
and c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
group by a.str_crm_dir,a.str_dir_name )cgdb
on ( cgdb.str_crm_dir = diqu.str_crm_dir)left outer join--营销中心
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.01%'
group by a.str_crm_dir,a.str_dir_name )yxzx
on (yxzx.str_crm_dir = diqu.str_crm_dir)
left outer join--医疗器械自营
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.02%'
and c.n_sellman_id=1375group by a.str_crm_dir,a.str_dir_name )ylqxzy
on (ylqxzy.str_crm_dir = diqu.str_crm_dir)
left outer join--医疗器械销售
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.02%'
and c.n_sellman_id!=1375group by a.str_crm_dir,a.str_dir_name )ylqxxs
on (ylqxxs.str_crm_dir = diqu.str_crm_dir)
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.03%'group by a.str_crm_dir,a.str_dir_name )xpyb
on (xpyb.str_crm_dir = diqu.str_crm_dir)
left outer join
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.04%'group by a.str_crm_dir,a.str_dir_name )xpsb
on (xpsb.str_crm_dir = diqu.str_crm_dir)
left outer join-- 产品开发部
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.05%'
on (cpkfb.str_crm_dir = diqu.str_crm_dir)
left outer join--中药部
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.06%'group by a.str_crm_dir,a.str_dir_name )zyb
on (zyb.str_crm_dir = diqu.str_crm_dir)
left outer join--医院代理部
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.07%'group by a.str_crm_dir,a.str_dir_name )yydlb
on (yydlb.str_crm_dir = diqu.str_crm_dir)
left outer join--保健品部
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.08%'group by a.str_crm_dir,a.str_dir_name )bjpb
on (bjpb.str_crm_dir = diqu.str_crm_dir)
left outer join--产品营运部营运自营
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.09%'
and c.n_sellman_id=1374group by a.str_crm_dir,a.str_dir_name )cpyybyyzx
on (cpyybyyzx.str_crm_dir = diqu.str_crm_dir)
left outer join--产品营运部营销中心销售
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.09%'
and c.n_sellman_id!=1374group by a.str_crm_dir,a.str_dir_name )cpyybyxzxxs
on (cpyybyxzxxs.str_crm_dir = diqu.str_crm_dir)
left outer join--新品二部
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.10%'group by a.str_crm_dir,a.str_dir_name )xpeb
on (xpeb.str_crm_dir = diqu.str_crm_dir)
left outer join --麻药部
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mllfrom
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.11%'group by a.str_crm_dir,a.str_dir_name )myylk
on (myylk.str_crm_dir = diqu.str_crm_dir)
left outer join--医院部自营
(select a.str_crm_dir,a.str_dir_name as str_dir_name,
sum(-d.d_money) as xse,
sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)) as ml,
(sum(-d.d_money)-sum(d.d_costing_price*(-d.n_amount)))/case sum( -d.d_money)when 0 then 1 else sum( -d.d_money) end as mll
from
v_account_stockintrade c (NoLock)
inner join v_account_stockintrade_detail d (NoLock)
on c.n_stockaccount_id=d.n_stockaccount_id
inner join t_account_dir e (NoLock)
on e.n_account_id=d.n_account_id
inner join t_crm_customers b (NoLock)
on b.n_crm_id=c.n_crm_id
inner join t_CRM_DIR a (NoLock)
on left(b.str_crm_dir,11)=a.str_crm_dir
where c.dt_chalkup between @dateup and @dateend
and a.str_crm_dir like '__.__.__.__%'
and c.str_operate_code in('006','106','606')
and e.str_depart_coding like'00.01.12%'group by a.str_crm_dir,a.str_dir_name )yyzyb
on (yyzyb.str_crm_dir = diqu.str_crm_dir)order by gxi.departcode,diqu.str_crm_dir
直接用异步执行试试,ExecuteComplete事件中判断执行完毕
适当建建索引,改ADOQuery.CursorLocation := clUseServer 试试