CREATE PROCEDURE [wzbb]
@ny varchar(2),
@nm varchar(2),
@py varchar(2),
@pm varchar(2)
AS
truncate table ttmp
insert ttmp(mingcheng,xinhao,danwei,danjia,pnum,pmoney,nrnum,nrmoney,ncnum,ncmoney,nknum,nkmoney)select a.产品名称,a.规格型号,a.单位,a.单价,isnull(a.库存数量+d.上月入库数量-e.上月数量,0) pnum,
isnull(a.金额+d.上月入库金额-e.上月总金额,0) pmoney,
isnull(b.入库数量,0) nrnum,
isnull(b.入库金额,0) nrmoney,
isnull(c.数量,0) ncnum,
isnull(c.总金额,0) ncmoney,
isnull(a.库存数量,0) nknum,
isnull(a.金额,0) nkmoneyfrom (select t_ck_tmp where year(a.入库日期)=@ny and month(a.入库日期)=@nm) aleft join (select 产品名称,规格型号,单价,sum(isnull(入库数量,0)) 入库数量,sum(isnull(入库金额,0)) 入库金额 from t_ck_rk where year(入库日期)=@ny and month(入库日期)=@nm group by 产品名称,规格型号,单价) b on a.产品名称=b.产品名称 and a.规格型号=b.规格型号 and a.单价=b.单价
left join (select 产品名称,规格型号,单价,sum(isnull(入库数量,0)) 上月入库数量,sum(isnull(入库金额,0)) 上月入库金额 from t_ck_rk where year(入库日期)=@py and month(入库日期)=@pm group by 产品名称,规格型号,单价) d on a.产品名称=b.产品名称 and a.规格型号=b.规格型号 and a.单价=b.单价
left join (select 产品名称,规格型号,单价,sum(isnull(数量,0)) 数量,sum(isnull(总金额,0)) 总金额 from t_ck_ll where year(领料日期)=@ny and month(领料日期)=@nm group by 产品名称,规格型号,单价) c on a.产品名称=c.产品名称 and a.规格型号=c.规格型号 and a.单价=c.单价
left join (select 产品名称,规格型号,单价,sum(isnull(数量,0)) 上月数量,sum(isnull(总金额,0)) 上月总金额 from t_ck_ll where year(领料日期)=@py and month(领料日期)=@pm group by 产品名称,规格型号,单价) e on a.产品名称=c.产品名称 and a.规格型号=c.规格型号 and a.单价=c.单价
GO
@ny varchar(2),
@nm varchar(2),
@py varchar(2),
@pm varchar(2)
AS
truncate table ttmp
insert ttmp(mingcheng,xinhao,danwei,danjia,pnum,pmoney,nrnum,nrmoney,ncnum,ncmoney,nknum,nkmoney)select a.产品名称,a.规格型号,a.单位,a.单价,isnull(a.库存数量+d.上月入库数量-e.上月数量,0) pnum,
isnull(a.金额+d.上月入库金额-e.上月总金额,0) pmoney,
isnull(b.入库数量,0) nrnum,
isnull(b.入库金额,0) nrmoney,
isnull(c.数量,0) ncnum,
isnull(c.总金额,0) ncmoney,
isnull(a.库存数量,0) nknum,
isnull(a.金额,0) nkmoneyfrom (select t_ck_tmp where year(a.入库日期)=@ny and month(a.入库日期)=@nm) aleft join (select 产品名称,规格型号,单价,sum(isnull(入库数量,0)) 入库数量,sum(isnull(入库金额,0)) 入库金额 from t_ck_rk where year(入库日期)=@ny and month(入库日期)=@nm group by 产品名称,规格型号,单价) b on a.产品名称=b.产品名称 and a.规格型号=b.规格型号 and a.单价=b.单价
left join (select 产品名称,规格型号,单价,sum(isnull(入库数量,0)) 上月入库数量,sum(isnull(入库金额,0)) 上月入库金额 from t_ck_rk where year(入库日期)=@py and month(入库日期)=@pm group by 产品名称,规格型号,单价) d on a.产品名称=b.产品名称 and a.规格型号=b.规格型号 and a.单价=b.单价
left join (select 产品名称,规格型号,单价,sum(isnull(数量,0)) 数量,sum(isnull(总金额,0)) 总金额 from t_ck_ll where year(领料日期)=@ny and month(领料日期)=@nm group by 产品名称,规格型号,单价) c on a.产品名称=c.产品名称 and a.规格型号=c.规格型号 and a.单价=c.单价
left join (select 产品名称,规格型号,单价,sum(isnull(数量,0)) 上月数量,sum(isnull(总金额,0)) 上月总金额 from t_ck_ll where year(领料日期)=@py and month(领料日期)=@pm group by 产品名称,规格型号,单价) e on a.产品名称=c.产品名称 and a.规格型号=c.规格型号 and a.单价=c.单价
GO
@ny varchar(2),
@nm varchar(2),
@py varchar(2),
@pm varchar(2)
AS
truncate table ttmp
insert ttmp(mingcheng,xinhao,danwei,danjia,pnum,pmoney,nrnum,nrmoney,ncnum,ncmoney,nknum,nkmoney)select a.产品名称,a.规格型号,a.单位,a.单价,isnull(a.库存数量+d.上月入库数量-e.上月数量,0) pnum,
isnull(a.金额+d.上月入库金额-e.上月总金额,0) pmoney,
isnull(b.入库数量,0) nrnum,
isnull(b.入库金额,0) nrmoney,
isnull(c.数量,0) ncnum,
isnull(c.总金额,0) ncmoney,
isnull(a.库存数量,0) nknum,
isnull(a.金额,0) nkmoneyfrom (select t_ck_tmp where year(入库日期)=@ny and month(入库日期)=@nm) aleft join (select 产品名称,规格型号,单价,sum(isnull(入库数量,0)) 入库数量,sum(isnull(入库金额,0)) 入库金额 from t_ck_rk where year(入库日期)=@ny and month(入库日期)=@nm group by 产品名称,规格型号,单价) b on a.产品名称=b.产品名称 and a.规格型号=b.规格型号 and a.单价=b.单价
left join (select 产品名称,规格型号,单价,sum(isnull(入库数量,0)) 上月入库数量,sum(isnull(入库金额,0)) 上月入库金额 from t_ck_rk where year(入库日期)=@py and month(入库日期)=@pm group by 产品名称,规格型号,单价) d on a.产品名称=b.产品名称 and a.规格型号=b.规格型号 and a.单价=b.单价
left join (select 产品名称,规格型号,单价,sum(isnull(数量,0)) 数量,sum(isnull(总金额,0)) 总金额 from t_ck_ll where year(领料日期)=@ny and month(领料日期)=@nm group by 产品名称,规格型号,单价) c on a.产品名称=c.产品名称 and a.规格型号=c.规格型号 and a.单价=c.单价
left join (select 产品名称,规格型号,单价,sum(isnull(数量,0)) 上月数量,sum(isnull(总金额,0)) 上月总金额 from t_ck_ll where year(领料日期)=@py and month(领料日期)=@pm group by 产品名称,规格型号,单价) e on a.产品名称=c.产品名称 and a.规格型号=c.规格型号 and a.单价=c.单价
GO
CREATE PROCEDURE [wzbb]
@ny varchar(2),
@nm varchar(2),
@py varchar(2),
@pm varchar(2)
AS
truncate table ttmp
insert ttmp(mingcheng,xinhao,danwei,danjia,pnum,pmoney,nrnum,nrmoney,ncnum,ncmoney,nknum,nkmoney)
select a.产品名称,a.规格型号,a.单位,a.单价,
isnull(a.库存数量+d.上月入库数量-e.上月数量,0) pnum,
isnull(a.金额+d.上月入库金额-e.上月总金额,0) pmoney,
isnull(b.入库数量,0) nrnum,
isnull(b.入库金额,0) nrmoney,
isnull(c.数量,0) ncnum,
isnull(c.总金额,0) ncmoney,
isnull(a.库存数量,0) nknum,
isnull(a.金额,0) nkmoney
from t_ck_tmp a
left join
(select 产品名称,规格型号,单价,sum(isnull(入库数量,0)) 入库数量,sum(isnull(入库金额,0)) 入库金额 from t_ck_rk where year(入库日期)=@ny and month(入库日期)=@nm group by 产品名称,规格型号,单价) b
on a.产品名称=b.产品名称 and a.规格型号=b.规格型号 and a.单价=b.单价
left join
(select 产品名称,规格型号,单价,sum(isnull(入库数量,0)) 上月入库数量,sum(isnull(入库金额,0)) 上月入库金额 from t_ck_rk where year(入库日期)=@py and month(入库日期)=@pm group by 产品名称,规格型号,单价) d
on a.产品名称=b.产品名称 and a.规格型号=b.规格型号 and a.单价=b.单价
left join
(select 产品名称,规格型号,单价,sum(isnull(数量,0)) 数量,sum(isnull(总金额,0)) 总金额 from t_ck_ll where year(领料日期)=@ny and month(领料日期)=@nm group by 产品名称,规格型号,单价) c
on a.产品名称=c.产品名称 and a.规格型号=c.规格型号 and a.单价=c.单价
left join
(select 产品名称,规格型号,单价,sum(isnull(数量,0)) 上月数量,sum(isnull(总金额,0)) 上月总金额 from t_ck_ll where year(领料日期)=@py and month(领料日期)=@pm group by 产品名称,规格型号,单价) e
on a.产品名称=c.产品名称 and a.规格型号=c.规格型号 and a.单价=c.单价
where year(a.入库日期)=convert(int,@ny) and month(a.入库日期)=convert(int,@nm)
要放在最后面