declare @begin_date as datetime select @begin_date = isNull((SELECT TOP 1 快照日期 FROM fact_t5 ORDER BY 快照日期 DESC), '11/13/2002')
declare @the_date as datetime select @the_date = dateadd(d,1,@begin_date)
declare @i as int select @i=0
while dateadd(d,@i,@the_date) < getdate()
Begin
insert into fact_t5
select * from
(
select
dateadd(d,@i,@the_date) as 快照日期
,a.仓库
,a.物料内码
,结存数量 = isnull((select 结存数量 from fact_t5 b where b.快照日期 = dateadd(d,-1,dateadd(d,@i,@the_date)) and a.物料内码=b.物料内码 and a.仓库=b.仓库),0)+isnull((select 入库数量 from fact_t4 c where c.单据日期 = dateadd(d,@i,@the_date) and a.物料内码=c.物料内码 and a.仓库=c.仓库),0)-isnull((select 出库数量 from fact_t4 d where d.单据日期 = dateadd(d,@i,@the_date) and a.物料内码=d.物料内码 and a.仓库=d.仓库 ),0)
,结存金额 = isnull((select 结存金额 from fact_t5 b where b.快照日期 = dateadd(d,-1,dateadd(d,@i,@the_date)) and a.物料内码=b.物料内码 and a.仓库=b.仓库),0)+isnull((select 入库金额 from fact_t4 c where c.单据日期 = dateadd(d,@i,@the_date) and a.物料内码=c.物料内码 and a.仓库=c.仓库),0)-isnull((select 出库金额 from fact_t4 d where d.单据日期 = dateadd(d,@i,@the_date) and a.物料内码=d.物料内码 and a.仓库=d.仓库 ),0)
from (
select distinct
仓库
,物料内码
from fact_t4 ) a
) s where s.结存数量 <> 0 and s.结存金额 <> 0
set @i=@i+1
End
