clyjc.sql

仓库管理信息系统

declare @nf integer,@yf integer 
set @nf=2003
set @yf=08

/*当月期初\进仓、出仓清0*/
update 材料每月结存表 set 上月数量=0,上月金额=0,进仓数量=0,
出仓数量=0,进仓金额=0,出仓金额=0,本月数量=0,本月金额=0
where 年份=@nf and 月份=@yf
/*添加当月新产品*/
insert into dbo.材料每月结存表(年份,月份,材料编码,类型id,材料名称,材料规格,单位) 
select @nf,@yf,材料表.材料编码,材料表.类型id,材料表.材料名称,材料表.材料规格,材料表.单位 from 材料表 left join 
(select 材料编码 from dbo.材料每月结存表 
where  dbo.材料每月结存表.年份=@nf and 
dbo.材料每月结存表.月份=@yf) as cpnb
on 材料表.材料编码=cpnb.材料编码 where cpnb.材料编码 is null
/*获取期初数*/
update 材料每月结存表 set 上月数量=mnqc.初始数量,
上月金额=mnqc.初始金额 
from (select 材料编码,初始数量,初始金额 from 材料每年库存表 
      where 年份=@nf) as mnqc
where 年份=@nf and 月份=@yf and 
mnqc.材料编码=材料每月结存表.材料编码
/*上月结存*/
update 材料每月结存表 set 上月数量=上月数量+djhz.数量,
上月金额=上月金额+djhz.金额 from 
(select 材料编码,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.材料编码,d.数量,d.金额 from dbo.材料进仓单 m 
inner join dbo.材料进仓明细表 d 
on m.进仓单号=d.进仓单号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)<@yf) as DJ
group by 材料编码) AS DJHZ 
where 材料每月结存表.年份=@nf and 材料每月结存表.月份=@yf and
 材料每月结存表.材料编码=DJHZ.材料编码
update 材料每月结存表 set 上月数量=上月数量-djhz.数量,
上月金额=上月金额-djhz.金额 from 
(select 材料编码,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.材料编码,d.数量,d.金额 from dbo.材料领料单 m 
inner join dbo.材料领料明细表 d 
on m.出仓单号=d.出仓单号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)<@yf) as DJ
group by 材料编码) AS DJHZ 
where 材料每月结存表.年份=@nf and 材料每月结存表.月份=@yf and
 材料每月结存表.材料编码=DJHZ.材料编码
/*更新进仓*/
update 材料每月结存表 set 进仓数量=djhz.数量,进仓金额=djhz.金额 from 
(select 材料编码,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.材料编码,d.数量,d.金额 from dbo.材料进仓单 m 
inner join dbo.材料进仓明细表 d 
on m.进仓单号=d.进仓单号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)=@yf) as DJ
group by 材料编码) AS DJHZ 
where 材料每月结存表.年份=@nf and 材料每月结存表.月份=@yf and
 材料每月结存表.材料编码=DJHZ.材料编码
/*更新出仓*/
update 材料每月结存表 set 出仓数量=djhz.数量,出仓金额=djhz.金额 from 
(select 材料编码,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.材料编码,d.数量,d.金额 from dbo.材料领料单 m 
inner join dbo.材料领料明细表 d 
on m.出仓单号=d.出仓单号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)=@yf) as DJ
group by 材料编码) AS DJHZ 
where 材料每月结存表.年份=@nf and 材料每月结存表.月份=@yf and
 材料每月结存表.材料编码=DJHZ.材料编码
/*更新库存*//*获取期初数*/
update 材料每月结存表 set 本月数量=mnqc.初始数量,
本月金额=mnqc.初始金额 
from (select 材料编码,初始数量,初始金额 from 材料每年库存表 
      where 年份=@nf) as mnqc
where 年份=@nf and 月份=@yf and 
mnqc.材料编码=材料每月结存表.材料编码
/*本月结存*/
update 材料每月结存表 set 本月数量=本月数量+djhz.数量,
本月金额=本月金额+djhz.金额 from 
(select 材料编码,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.材料编码,d.数量,d.金额 from dbo.材料进仓单 m 
inner join dbo.材料进仓明细表 d 
on m.进仓单号=d.进仓单号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)<=@yf) as DJ
group by 材料编码) AS DJHZ 
where 材料每月结存表.年份=@nf and 材料每月结存表.月份=@yf and
 材料每月结存表.材料编码=DJHZ.材料编码
update 材料每月结存表 set 本月数量=本月数量-djhz.数量,
本月金额=本月金额-djhz.金额 from 
(select 材料编码,sum(数量) as 数量,sum(金额) as 金额 from 
(select d.材料编码,d.数量,d.金额 from dbo.材料领料单 m 
inner join dbo.材料领料明细表 d 
on m.出仓单号=d.出仓单号
where datepart(year,m.日期)=@nf and datepart(month,m.日期)<=@yf) as DJ
group by 材料编码) AS DJHZ 
where 材料每月结存表.年份=@nf and 材料每月结存表.月份=@yf and
 材料每月结存表.材料编码=DJHZ.材料编码
GO