环境：MSSQL2000,求库存台帐报表,按日期出入库计算结存。
视图VIEW数据billflow库存底稿
日期，单据ID,明细序号，单号，仓库，物料，数量，出入库标记
billdate,billid,itemno,billcode,storeid,materialid,quantity,ioflag
2013-01-03, 1003, 1, PC001, 原材料仓, A008, 900, 1
2013-01-03, 1003, 2, PC001, 原材料仓, A011, 700, 1
2013-01-03, 1004, 1, PC007, 原材料仓, A008, 300, 1
2013-01-03, 1007, 1, PC008, 半成品仓, A008, 123, 1 -----这里是半成品仓
2013-01-07, 1003, 1, PC006, 原材料仓, A008, 200, -1 ----- -1是出仓
2013-01-08, 1003, 1, PC003, 原材料仓, A008, 600, 1
说明：ioflag=1表示进仓，ioflag=-1表示出仓
当过滤日期2013-01-01,2013-01-08,仓库为原材料仓时 ,
根据不同的开始日期、结束日期过滤查询则会有相应的变化
billtype,billdate,billcode,storeid,materialid,inqty,outqty,balcqty
期初，2013-01-01,NULL ,原材料仓,A008 , 0 , 0 , 0
进仓, 2013-01-03,pc001 ,原材料仓,A008 , 900 , 0 , 900
进仓, 2013-01-03,pc007 ,原材料仓,A008 , 300 , 0 , 1200
出仓, 2013-01-07,pc006 ,原材料仓,A008 , 0 , 200 , 1000
进仓, 2013-01-08,pc003 ,原材料仓,A008 , 600 , 0 , 1600
结存, 2013-01-08 NULL ,原材料仓,A008 , 0 , 0 , 1600
期初，2013-01-01,NULL ,半成品仓,A008 , 0 , 0 , 0
进仓, 2013-01-03,pc008 ,半成品仓,A008 , 123 , 0 , 123
结存, 2013-01-08 NULL ,半成品仓,A008 , 0 , 0 , 123
期初，2013-01-01,NULL ,原材料仓,A011 , 0 , 0 , 0
进仓, 2013-01-03,pc001 ,原材料仓,A011 , 700 , 0 , 700
结存, 2013-01-08 NULL ,原材料仓,A011 , 0 , 0 , 700
------解决思路----------------------
--create table t (
--billdate datetime,billid int,itemno int,billcode nvarchar(10),storeid nvarchar(10),materialid nvarchar(10),quantity int ,ioflag int
--)
--
--insert into t
--select '2013-01-03', 1003, 1, 'PC001', '原材料仓', 'A008', 900, 1 union all
--select '2013-01-03', 1003, 2, 'PC001', '原材料仓', 'A011', 700, 1 union all
--select '2013-01-03', 1004, 1, 'PC007', '原材料仓', 'A008', 300, 1 union all
--select '2013-01-03', 1007, 1, 'PC008', '半成品仓', 'A008', 123, 1 union all
--select '2013-01-07', 1003, 1, 'PC006', '原材料仓', 'A008', 200, -1 union all
--select '2013-01-08', 1003, 1, 'PC003', '原材料仓', 'A008', 600, 1
--
--
declare @startdate datetime,@enddate datetime
set @startdate='2013-01-01'
set @enddate='2013-01-08'
;with t1 as (
select *,
seq1 =row_number()over(partition by temp1.materialid,temp1.storeid order by billdate),
seq2 =rank()over(order by temp1.materialid,temp1.storeid )
from
(
select * from t
where billdate between @startdate and @enddate
union
select distinct date,0,0,'',storeid,materialid,0,case when date=@startdate then 0 else 99 end
from t,(select @startdate as date union select @enddate) temp
where billdate between @startdate and @enddate) temp1
)
select
case t1.ioflag when 0 then '初期'
when 1 then '进仓'
when -1 then '出仓'
when 99 then '结存' end as col1,
t1.billdate,
billcode,
storeid,materialid,
t1.quantity*(case t1.ioflag when 1 then 1 else 0 end) ,
t1.quantity*(case t1.ioflag when -1 then 1 else 0 end),
(select sum(t2.quantity*(case t2.ioflag when 99 then 0 else t2.ioflag end)) from t1 t2 where t2.seq2=t1.seq2 and t2.seq1<=t1.seq1),
seq1,seq2
from t1
order by seq2,seq1