动态时间段收发存报表

http://topic.csdn.net/u/20090831/10/a850c8a4-c733-459f-8d33-a9523e73ad52.html

/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-08-31 11:00:29
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

---------------------------------*/
--> 生成测试数据表:IO_STORE

If not object_id('[IO_STORE]') is null
Drop table [IO_STORE]
Go
Create table [IO_STORE]([STORE] nvarchar(4),[BD] Datetime,[BC] nvarchar(10),[GOODS] nvarchar(6),[QT] int,[ISIND] int)
Insert [IO_STORE]
Select N'半成品仓','2009-07-01',N'090701001',N'16081D',300,1 union all
Select N'半成品仓','2009-07-02',N'090702003',N'16081D',50,1 union all
Select N'半成品仓','2009-07-18',N'090718003',N'16081D',90,1 union all
Select N'半成品仓','2009-07-18',N'090718004',N'16081D',200,1 union all
Select N'半成品仓','2009-07-01',N'090701002',N'16082A',50,1 union all
Select N'半成品仓','2009-07-15',N'090715007',N'16083K',80,1 union all
Select N'成品仓','2009-07-02',N'090702007',N'16081D',50,1 union all
Select N'成品仓','2009-07-03',N'090703008',N'16081D',77,1 union all
Select N'半成品仓','2009-07-05',N'090705001',N'16081D',30,0 union all
Select N'半成品仓','2009-07-06',N'090706002',N'16082A',20,0 union all
Select N'半成品仓','2009-07-18',N'090718006',N'16082A',20,0 union all
Select N'半成品仓','2009-07-23',N'090723007',N'16083K',35,0 union all
Select N'成品仓','2009-07-28',N'090728026',N'16081D',20,0
Go
--Select * from [IO_STORE]

-->SQL查询如下:
If not object_id('[R_I_INOUTSTOR]') is null
Drop FUNCTION [R_I_INOUTSTOR]
Go
CREATE FUNCTION R_I_INOUTSTOR(@FROMDATE DATETIME, @TODATE DATETIME)
RETURNS @TempTable TABLE
(
ID int identity,
STORE VARCHAR(80), --仓库
BD DATETIME, --日期
BC VARCHAR(80),--单号
GOODSID VARCHAR(80) ,--货品
SQT FLOAT , --期初
IQT FLOAT , --收入
OQT FLOAT ,--发出
CQT FLOAT --结存
)
AS
BEGIN
INSERT @TempTable
SELECT STORE,@FROMDATE,'期初',GOODS,
sum(case ISIND when 1 then QT else -QT end),0,0,
sum(case ISIND when 1 then QT else -QT end)
FROM [IO_STORE]
WHERE [BD]<@FROMDATE
GROUP BY STORE,GOODS

INSERT @TempTable
SELECT STORE,BD,BC,GOODS,0,
SUM(case ISIND WHEN 1 then QT else 0 end) IQT,
SUM(case ISIND WHEN 0 then QT else 0 end) OQT,
0
FROM [IO_STORE]
WHERE [BD] BETWEEN @FROMDATE AND @TODATE
GROUP BY STORE,BD,BC,GOODS

UPDATE t SET
SQT=CASE WHEN SQT=0 THEN (SELECT SUM(CQT+IQT-OQT) from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID AND ID<=t.ID)
ELSE SQT
END-IQT+OQT
FROM @TempTable t

UPDATE @TempTable SET
CQT=SQT+IQT-OQT
WHERE CQT=0
INSERT @TempTable
SELECT STORE,
(select top 1 BD from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID order by ID DESC),
'结存',GOODSID,
(select SQT from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID and BC='期初'),
sum(IQT),sum(OQT),
(select top 1 CQT from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID order by ID DESC)
FROM @TempTable t
WHERE [BD] BETWEEN @FROMDATE AND @TODATE
GROUP BY STORE,GOODSID
RETURN
END
GO
--调用查询
select STORE,convert(varchar(10),BD,23) BD,BC,GOODSID,SQT,IQT,OQT,CQT
from R_I_INOUTSTOR('2009-07-03','2009-07-28')
order by STORE,GOODSID,ID
/*
STOREBDBCGOODSIDSQTIQTOQTCQT
半成品仓2009-07-03期初16081D35000350
半成品仓2009-07-0509070500116081D350030320
半成品仓2009-07-1809071800316081D320900410
半成品仓2009-07-1809071800416081D4102000610
半成品仓2009-07-18结存16081D35029030610
半成品仓2009-07-03期初16082A500050
半成品仓2009-07-0609070600216082A5002030
半成品仓2009-07-1809071800616082A3002010
半成品仓2009-07-18结存16082A5004010
半成品仓2009-07-1509071500716083K080080
半成品仓2009-07-2309072300716083K8003545
半成品仓2009-07-23结存16083KNULL803545
成品仓2009-07-03期初16081D500050
成品仓2009-07-0309070300816081D50770127
成品仓2009-07-2809072802616081D127020107
成品仓2009-07-28结存16081D507720107
*/