--客户明细
create proc wfg_sp_T_Details(@iYear int ,@iPeriod int ,@iRegion varchar(150))
as
set nocount on
set ansi_warnings off
--定义当月数量和金额,累计数量和金额,汇率
declare @iQty decimal(18,2), @iAmt decimal(18,2) ,@ifqtyone decimal(18,2),@iZQty decimal(18,2) ,@iZAmt decimal(18,2)--查出当月数量金额总数
select @iQty=sum(fqty),@iAmt=sum(famt) from wfg_t_B_Detail_Basic where fyear=@iYear and fperiod=@iPeriod and FRegion=@iRegion
--查出累计数量金额总数
select @iZQty=sum(fqty),@iZAmt=sum(famt) from wfg_t_B_Detail_Basic where fyear=@iYear and fperiod<=@iPeriod and FRegion=@iRegion
--查汇率
select @ifqtyone=isnull(FQtyOne,0) from wfg_t_B_ExchangeRate where FMoneyOne='美元'select b.FRegion '事业部',
b.FCustomer '客户名称',
sum(a.fqty) '销售数量',
case when sum(a.fAmt)=0 then 0 else convert(decimal(18,2),(sum(a.fAmt)/@ifqtyone)) end '销售收入(美元)',
sum(a.fAmt) '销售收入(人民币)',
case when sum(a.fAmt)=0 then convert(varchar(100),0) else convert(varchar(200),(convert(decimal(18,2),round(sum(a.famt)/@iAmt*100,2))))+'%' end '收入比例',
b.fdqty '累计销售数量',
case when b.fdamt=0 then 0 else convert(decimal(18,2),b.fdamt/@ifqtyone) end '累计销售收入(美元)',
b.fdamt '累计销售收入(人民币)',
case when b.fdamt=0 then convert(varchar(100),0) else convert(varchar(200),(convert(decimal(18,2),round(b.fdamt/@iZAmt*100,2))))+'%' end '累计收入比例'
from wfg_t_B_Detail_Basic a
right join
(select sum(fqty) as fdqty,sum(famt) as fdamt, FCustomer,FRegion from wfg_t_B_Detail_Basic where fyear=@iYear and fperiod<=@iPeriod group by FCustomer) b
on a.FCustomer=b.FCustomer where a.fyear=@iYear and a.fperiod=@iPeriod and b.FRegion=@iRegion
group by b.FCustomer,b.fdqty,b.fdamt,b.FRegion,a.famt消息 8120,级别 16,状态 1,过程 wfg_sp_T_Details,第 16 行
选择列表中的列 'wfg_t_B_Detail_Basic.FRegion' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
create proc wfg_sp_T_Details(@iYear int ,@iPeriod int ,@iRegion varchar(150))
as
set nocount on
set ansi_warnings off
--定义当月数量和金额,累计数量和金额,汇率
declare @iQty decimal(18,2), @iAmt decimal(18,2) ,@ifqtyone decimal(18,2),@iZQty decimal(18,2) ,@iZAmt decimal(18,2)--查出当月数量金额总数
select @iQty=sum(fqty),@iAmt=sum(famt) from wfg_t_B_Detail_Basic where fyear=@iYear and fperiod=@iPeriod and FRegion=@iRegion
--查出累计数量金额总数
select @iZQty=sum(fqty),@iZAmt=sum(famt) from wfg_t_B_Detail_Basic where fyear=@iYear and fperiod<=@iPeriod and FRegion=@iRegion
--查汇率
select @ifqtyone=isnull(FQtyOne,0) from wfg_t_B_ExchangeRate where FMoneyOne='美元'select b.FRegion '事业部',
b.FCustomer '客户名称',
sum(a.fqty) '销售数量',
case when sum(a.fAmt)=0 then 0 else convert(decimal(18,2),(sum(a.fAmt)/@ifqtyone)) end '销售收入(美元)',
sum(a.fAmt) '销售收入(人民币)',
case when sum(a.fAmt)=0 then convert(varchar(100),0) else convert(varchar(200),(convert(decimal(18,2),round(sum(a.famt)/@iAmt*100,2))))+'%' end '收入比例',
b.fdqty '累计销售数量',
case when b.fdamt=0 then 0 else convert(decimal(18,2),b.fdamt/@ifqtyone) end '累计销售收入(美元)',
b.fdamt '累计销售收入(人民币)',
case when b.fdamt=0 then convert(varchar(100),0) else convert(varchar(200),(convert(decimal(18,2),round(b.fdamt/@iZAmt*100,2))))+'%' end '累计收入比例'
from wfg_t_B_Detail_Basic a
right join
(select sum(fqty) as fdqty,sum(famt) as fdamt, FCustomer,FRegion from wfg_t_B_Detail_Basic where fyear=@iYear and fperiod<=@iPeriod group by FCustomer) b
on a.FCustomer=b.FCustomer where a.fyear=@iYear and a.fperiod=@iPeriod and b.FRegion=@iRegion
group by b.FCustomer,b.fdqty,b.fdamt,b.FRegion,a.famt消息 8120,级别 16,状态 1,过程 wfg_sp_T_Details,第 16 行
选择列表中的列 'wfg_t_B_Detail_Basic.FRegion' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
b.FCustomer '客户名称',
sum(a.fqty) '销售数量',
case when sum(a.fAmt)=0 then 0 else convert(decimal(18,2),(sum(a.fAmt)/@ifqtyone)) end '销售收入(美元)',
sum(a.fAmt) '销售收入(人民币)',
case when sum(a.fAmt)=0 then convert(varchar(100),0) else convert(varchar(200),(convert(decimal(18,2),round(sum(a.famt)/@iAmt*100,2))))+'%' end '收入比例',
b.fdqty '累计销售数量',
case when b.fdamt=0 then 0 else convert(decimal(18,2),b.fdamt/@ifqtyone) end '累计销售收入(美元)',
b.fdamt '累计销售收入(人民币)',
case when b.fdamt=0 then convert(varchar(100),0) else convert(varchar(200),(convert(decimal(18,2),round(b.fdamt/@iZAmt*100,2))))+'%' end '累计收入比例'
from wfg_t_B_Detail_Basic a
right join
(select sum(fqty) as fdqty,sum(famt) as fdamt,FCustomer,FRegion from wfg_t_B_Detail_Basic where fyear=@iYear and fperiod<=@iPeriod group by FCustomer,FRegion) b
on a.FCustomer=b.FCustomer where a.fyear=@iYear and a.fperiod=@iPeriod and b.FRegion=@iRegion
group by b.FCustomer,b.fdqty,b.fdamt,b.FRegion,a.famt
试下