6.4.1 库存明细帐处理示例(包含结存数).sql

sqlserver 数据库编程的绝好脚本

--结存表
CREATE TABLE Stocks(Item varchar(10),Period int,Balance int)
INSERT Stocks SELECT 'aa',200501,100
UNION  ALL    SELECT 'cc',200501,100

--明细账数据
CREATE TABLE tb(
ID int IDENTITY PRIMARY KEY,
Item varchar(10),  --产品编号
Quantity int,      --交易数量
Flag bit,          --交易标志,1代表入库,0代表出库,这样可以有效区分退货(负数)
Date datetime)     --交易日期
INSERT tb SELECT 'aa',100,1,'2005-1-1'
UNION ALL SELECT 'aa',90 ,1,'2005-2-1'
UNION ALL SELECT 'aa',55 ,0,'2005-2-1'
UNION ALL SELECT 'aa',-10,1,'2005-2-2'
UNION ALL SELECT 'aa',-5 ,0,'2005-2-3'
UNION ALL SELECT 'aa',200,1,'2005-2-2'
UNION ALL SELECT 'aa',90 ,1,'2005-2-1'
UNION ALL SELECT 'bb',95 ,1,'2005-2-2'
UNION ALL SELECT 'bb',65 ,0,'2005-2-3'
UNION ALL SELECT 'bb',-15,1,'2005-2-5'
UNION ALL SELECT 'bb',-20,0,'2005-2-5'
UNION ALL SELECT 'bb',100,1,'2005-2-7'
UNION ALL SELECT 'cc',100,1,'2005-1-7'
GO

--查询时间段定义
DECLARE @dt1 datetime,@dt2 datetime
SELECT @dt1='2005-2-2',@dt2='2005-2-10'

--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @Period int,@dt datetime
SELECT @Period=CONVERT(CHAR(6),DATEADD(Month,-1,@dt1),112),
	@dt=DATEADD(Day,1-Day(@dt1),@dt1)

--查询期初库存
SELECT Item=ISNULL(a.Item,b.Item),
	Date=ISNULL(b.Date,CONVERT(char(10),@dt1,120)),
	Opening=ISNULL(a.Balance,0)+ISNULL(b.Opening,0),
	[IN]=ISNULL(b.[IN],0),
	[IN_Retrun]=ISNULL(b.[IN_Retrun],0),
	[OUT]=ISNULL(b.[OUT],0),
	[OUT_Return]=ISNULL(b.[OUT_Return],0),
	Balance=ISNULL(a.Balance,0)+ISNULL(b.Opening,0)+ISNULL(b.Amount,0)
FROM(
	--期初数
	SELECT Item,Balance FROM Stocks WHERE Period=@Period
)a FULL JOIN(
	--统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)
	SELECT Item,
		Date=CONVERT(char(10),@dt1,120),	
		Opening=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END),
		[IN]=0,
		[IN_Retrun]=0,
		[OUT]=0,
		[OUT_Return]=0,
		Amount=0
	FROM tb a
	WHERE Date>=@dt AND Date<@dt1 
		AND NOT EXISTS(
			SELECT * FROM tb WHERE Item=a.Item AND Date>@dt1 AND Date<DATEADD(Day,1,@dt2))
	GROUP BY Item
	UNION ALL
	--指定时间段内有交易发生的数据
	SELECT Item,
		Date=CONVERT(char(10),Date,120),	
		Opening=(SELECT SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)
			FROM tb WHERE Item=a.Item AND Date>=@dt AND Date<MIN(a.Date)),
		[IN]=SUM(CASE WHEN Flag=1 AND Quantity>0 THEN Quantity END),
		[IN_Retrun]=SUM(CASE WHEN Flag=1 AND Quantity<0 THEN -Quantity END),
		[OUT]=SUM(CASE WHEN Flag=0 AND Quantity>0 THEN Quantity END),
		[OUT_Return]=SUM(CASE WHEN Flag=0 AND Quantity<0 THEN -Quantity END),
		Amount=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)
	FROM tb a
	WHERE Date>=@dt1 AND Date<DATEADD(Day,1,@dt2)
	GROUP BY CONVERT(char(10),Date,120),Item
)b ON a.Item=b.Item
ORDER BY Item,Date
/*--结果
Item    Date       Opening    IN     IN_Retrun   OUT   OUT_Return   Balance 
---------- ---------------- -------------- ----------- ---------------- ----------- ------------------- ----------- 
aa     2005-02-02   225      200     10          0       0            415
aa     2005-02-03   415      0       0           0       5            420
bb     2005-02-02   0        95      0           0       0            95
bb     2005-02-03   95       0       0           65      0            30
bb     2005-02-05   30       0       15          0       20           35
bb     2005-02-07   35       100     0           0       0            135
cc     2005-02-01   100       0      0           0       0            100
--*/