按日统计销售情况例子(每天的期末值是下一天的期初值)重要

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
--select * from TB

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

--查询
--统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)
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,
 Balance=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)
FROM tb a
WHERE 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=ISNULL((SELECT SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)
  FROM tb WHERE Item=a.Item AND Date<MIN(a.Date)),0),
 [IN]=ISNULL(SUM(CASE WHEN Flag=1 AND Quantity>0 THEN Quantity END),0),
 [IN_Retrun]=ISNULL(SUM(CASE WHEN Flag=1 AND Quantity<0 THEN -Quantity END),0),
 [OUT]=ISNULL(SUM(CASE WHEN Flag=0 AND Quantity>0 THEN Quantity END),0),
 [OUT_Return]=ISNULL(SUM(CASE WHEN Flag=0 AND Quantity<0 THEN -Quantity END),0),
 Balance=ISNULL((SELECT SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)
  FROM tb WHERE Item=a.Item AND Date<=MAX(a.Date)),0)
FROM tb a
WHERE Date>=@dt1 AND Date<DATEADD(Day,1,@dt2)
GROUP BY CONVERT(char(10),Date,120),Item
ORDER BY Item,Date