cljc.sql

仓库管理信息系统

declare @nf integer
declare @yf integer
set @nf=2003
set @yf=08 
select jcdjhz.材料编码,jcdjhz.进仓数量,jcdjhz.进仓金额,ccdjhz.出仓数量,ccdjhz.出仓金额 from     
   (select 材料编码,sum(数量) as 进仓数量,sum(金额) as 进仓金额 from 
       (select jcd.材料编码,jcd.数量,jcd.金额 from dbo.材料进仓单 jcm 
        inner join dbo.材料进仓明细表 jcd 
        on jcm.进仓单号=jcd.进仓单号
        where datepart(year,jcm.日期)=@nf and 
        datepart(month,jcm.日期)=@yf) as jcDJ
        group by 材料编码) AS jcDJHZ full join 
    (select 材料编码,sum(数量) as 出仓数量,sum(金额) as 出仓金额 from 
       (select ccd.材料编码,ccd.数量,ccd.金额 from dbo.材料领料单 ccm
        inner join dbo.材料领料明细表 ccd
        on ccm.出仓单号=ccd.出仓单号
        where datepart(year,ccm.日期)=@nf and 
        datepart(month,ccm.日期)=@yf) as ccDJ
        group by 材料编码) AS ccDJHZ 
    on jcdjhz.材料编码=ccdjhz.材料编码