select 货号,品名,datetime,sum(入库量),sum(出库量),sum(入库量)-sum(出库量)
from a ,b,c
where a.货号=b.货号 and a.货号=c.货号
and b.日期<datetime and c.日期<datetime
group by 货号,品名
from a ,b,c
where a.货号=b.货号 and a.货号=c.货号
and b.日期<datetime and c.日期<datetime
group by 货号,品名
货号,品名
B:
货号,日期,出库量
C:
货号,日期,入库量
select A.*,c.日期,d.入庫數,e.出庫數,結余自已現算了
from A inner join (select Distinct * from(select b.货号,b.日期 from B as b Union select a.货号,a.日期 from C as a) as c)as c
On A.貨號=c.貨號
left join B as e On A.貨號=e.貨號 Left Join C as d On A.貨號=d.貨號
A.貨號=b.
((select 入库量=isnull(sum(入库量),0) from C where 货号=t1.货号 and convert(varchar(10),日期,120)<=t1.日期) - (select 出库量=isnull(sum(出库量),0) from B where 货号=t1.货号 and convert(varchar(10),日期,120)<=t1.日期))as 结余
from
(select 货号,日期=convert(varchar(10),日期,120),出入量=sum(出库量),min(-1) as flag from B group by 货号,convert(varchar(10),日期,120)
union all
select 货号,日期=convert(varchar(10),日期,120),出入量=sum(入库量),min( 1) as flag from C group by 货号,convert(varchar(10),日期,120)) t1 left join A t2 on t1.货号=t2.货号
group by t1.货号,t1.日期
order by t1.货号,t1.日期
from a,(
select isnull(b1.日期,c1.日期) as 日期,isnull(b1.货号,c1.货号) as 货号,
isnull(b1.入库量,0) as 入库量,isnull(c1.出库量,0) as 出库量,
(select sum(入库量) from b where 货号=isnull(b1.货号,c1.货号) and 日期<=isnull(b1.日期,c1.日期))-(select sum(出库量) from c where 货号=isnull(b1.货号,c1.货号) and 日期<=isnull(b1.日期,c1.日期)) as 结余
from b1 full join c1
on b.货号=c.货号 and b.日期=c.日期
) as x
where a.货号=x.货号
group by a.货号,x.日期