CREATE procedure spNZB_JBCFY(
@pBeginRQ datetime, @pEndRQ datetime,
@pCLBM varchar(20)='',
@pFL varchar(10)=''
) with encryption
as
if isnull(@pCLBM, '')='' set @pCLBM='%'
if @pFL='0' --------------------按出库时间分类汇总
begin
select [出库日期]=z.dtckrq,[车辆编码]=x.vcch,[商品编码]=x.vcspbm,[商品名称]=h.vcchmc,[
数量]=sum(x.fcksl),[单价]=x.fckdj,[金额]=sum(x.fckje)
from tbCKZB z inner join tbCKXB x on x.vcZBDJBH=z.vcZBDJBH
left join tbCHDA h on h.vcSPBM=x.vcSPBM
where isnull(z.vcdjlxbm, '')='15' AND isnull(z.vcshr,'')<>'' and isnull(x.vcch,'')<>''
and (z.vcckbm='19#' or z.vcckbm='22#')
and z.dtckrq between @pBeginRQ and @pEndRQ
and isnull(x.vcCH, '') like @pCLBM
group by z.dtckrq,x.vcch,x.vcspbm,h.vcchmc,x.fckdj
order by z.dtckrq,x.vcch,x.vcspbm,h.vcchmc,x.fckdj
end
else
begin --------------------按车辆编码分类汇总
select [车辆编码]=x.vcch,[商品编码]=x.vcspbm,[商品名称]=h.vcchmc,[数量]=sum(x.fcksl),[单价]=x.fckdj,[金额]=sum(x.fckje)
from tbCKZB z inner join tbCKXB x on x.vcZBDJBH=z.vcZBDJBH
left join tbCHDA h on h.vcSPBM=x.vcSPBM
where isnull(z.vcdjlxbm, '')='15' AND isnull(z.vcshr,'')<>'' and isnull(x.vcch,'')<>''
and (z.vcckbm='19#' or z.vcckbm='22#')
and z.dtckrq between @pBeginRQ and @pEndRQ
and isnull(x.vcCH, '') like @pCLBM
group by x.vcch,x.vcspbm,h.vcchmc,x.fckdj
order by x.vcch,x.vcspbm,h.vcchmc,x.fckdj
end----运行过程
exec spNZB_JBCFY '2007-12-18 00:00:00','2007-12-18 23:59:59','','0'----结果
出库日期 车辆编号 商品编码 商品名称 数量 单价 金额
2007-12-18 14:10:03.107 JB01052 P2800041 大力神油门拉线 1.0000 45.0000 45.0000
2007-12-18 14:10:03.107 JB01052 P2800046 小太阳射灯 2.0000 15.0000 30.0000
2007-12-18 14:10:03.107 JB01060 P2800054 大力神车用水泵 1.0000 950.0000 950.0000我要怎么修改存储过程才能 添加一列 查出 同一车辆编号的 总金额
@pBeginRQ datetime, @pEndRQ datetime,
@pCLBM varchar(20)='',
@pFL varchar(10)=''
) with encryption
as
if isnull(@pCLBM, '')='' set @pCLBM='%'
if @pFL='0' --------------------按出库时间分类汇总
begin
select [出库日期]=z.dtckrq,[车辆编码]=x.vcch,[商品编码]=x.vcspbm,[商品名称]=h.vcchmc,[
数量]=sum(x.fcksl),[单价]=x.fckdj,[金额]=sum(x.fckje)
from tbCKZB z inner join tbCKXB x on x.vcZBDJBH=z.vcZBDJBH
left join tbCHDA h on h.vcSPBM=x.vcSPBM
where isnull(z.vcdjlxbm, '')='15' AND isnull(z.vcshr,'')<>'' and isnull(x.vcch,'')<>''
and (z.vcckbm='19#' or z.vcckbm='22#')
and z.dtckrq between @pBeginRQ and @pEndRQ
and isnull(x.vcCH, '') like @pCLBM
group by z.dtckrq,x.vcch,x.vcspbm,h.vcchmc,x.fckdj
order by z.dtckrq,x.vcch,x.vcspbm,h.vcchmc,x.fckdj
end
else
begin --------------------按车辆编码分类汇总
select [车辆编码]=x.vcch,[商品编码]=x.vcspbm,[商品名称]=h.vcchmc,[数量]=sum(x.fcksl),[单价]=x.fckdj,[金额]=sum(x.fckje)
from tbCKZB z inner join tbCKXB x on x.vcZBDJBH=z.vcZBDJBH
left join tbCHDA h on h.vcSPBM=x.vcSPBM
where isnull(z.vcdjlxbm, '')='15' AND isnull(z.vcshr,'')<>'' and isnull(x.vcch,'')<>''
and (z.vcckbm='19#' or z.vcckbm='22#')
and z.dtckrq between @pBeginRQ and @pEndRQ
and isnull(x.vcCH, '') like @pCLBM
group by x.vcch,x.vcspbm,h.vcchmc,x.fckdj
order by x.vcch,x.vcspbm,h.vcchmc,x.fckdj
end----运行过程
exec spNZB_JBCFY '2007-12-18 00:00:00','2007-12-18 23:59:59','','0'----结果
出库日期 车辆编号 商品编码 商品名称 数量 单价 金额
2007-12-18 14:10:03.107 JB01052 P2800041 大力神油门拉线 1.0000 45.0000 45.0000
2007-12-18 14:10:03.107 JB01052 P2800046 小太阳射灯 2.0000 15.0000 30.0000
2007-12-18 14:10:03.107 JB01060 P2800054 大力神车用水泵 1.0000 950.0000 950.0000我要怎么修改存储过程才能 添加一列 查出 同一车辆编号的 总金额
@pBeginRQ datetime, @pEndRQ datetime,
@pCLBM varchar(20)='',
@pFL varchar(10)=''
) with encryption
as
if isnull(@pCLBM, '')='' set @pCLBM='%'
if @pFL='0' --------------------按出库时间分类汇总
begin
select
[出库日期]=z.dtckrq,[车辆编码]=x.vcch,[商品编码]=x.vcspbm,[商品名称]=h.vcchmc,[数量]=sum(x.fcksl),[单价]=x.fckdj,[金额]=sum(x.fckje),
[总金额]=(select sum(x.fckje) from tbCKXB where vcZBDJBH=x.vcZBDJBH and vcch=x.vcch)
from tbCKZB z inner join tbCKXB x on x.vcZBDJBH=z.vcZBDJBH
left join tbCHDA h on h.vcSPBM=x.vcSPBM
where isnull(z.vcdjlxbm, '')='15' AND isnull(z.vcshr,'') <>'' and isnull(x.vcch,'') <>''
and (z.vcckbm='19#' or z.vcckbm='22#')
and z.dtckrq between @pBeginRQ and @pEndRQ
and isnull(x.vcCH, '') like @pCLBM
group by z.dtckrq,x.vcch,x.vcspbm,h.vcchmc,x.fckdj
order by z.dtckrq,x.vcch,x.vcspbm,h.vcchmc,x.fckdj
end
else
begin --------------------按车辆编码分类汇总
select [车辆编码]=x.vcch,[商品编码]=x.vcspbm,[商品名称]=h.vcchmc,[数量]=sum(x.fcksl),[ 单价]=x.fckdj,[金额]=sum(x.fckje),
[总金额]=(select sum(x.fckje) from tbCKXB where vcZBDJBH=x.vcZBDJBH and vcch=x.vcch)
from tbCKZB z inner join tbCKXB x on x.vcZBDJBH=z.vcZBDJBH
left join tbCHDA h on h.vcSPBM=x.vcSPBM
where isnull(z.vcdjlxbm, '')='15' AND isnull(z.vcshr,'') <>'' and isnull(x.vcch,'') <>''
and (z.vcckbm='19#' or z.vcckbm='22#')
and z.dtckrq between @pBeginRQ and @pEndRQ
and isnull(x.vcCH, '') like @pCLBM
group by x.vcch,x.vcspbm,h.vcchmc,x.fckdj
order by x.vcch,x.vcspbm,h.vcchmc,x.fckdj
end
select x.vcch,from tbCKZB z inner join tbCKXB x on x.vcZBDJBH=z.vcZBDJBH