按日统计,销售情况例子

/*--示例

--出处:邹建

在代码中，首先定义了一个最简单的出入库数据记录明细表（tb），用来记录每笔出入库的交易情况。

明细帐查询要求得到每种Item每天的期初数量、当天进货数、进货退回数、出货数、出货退回数及当天结余数。

--*/

--明细帐数据

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

--结果

ID Item Quantity Flag Date

----------- ---------- ----------- ---- ------------------------------------------------------

1 aa 100 1 2005-01-01 00:00:00.000

2 aa 90 1 2005-02-01 00:00:00.000

3 aa 55 0 2005-02-01 00:00:00.000

4 aa -10 1 2005-02-02 00:00:00.000

5 aa -5 0 2005-02-03 00:00:00.000

6 aa 200 1 2005-02-02 00:00:00.000

7 aa 90 1 2005-02-01 00:00:00.000

8 bb 95 1 2005-02-02 00:00:00.000

9 bb 65 0 2005-02-03 00:00:00.000

10 bb -15 1 2005-02-05 00:00:00.000

11 bb -20 0 2005-02-05 00:00:00.000

12 bb 100 1 2005-02-07 00:00:00.000

13 cc 100 1 2005-01-07 00:00:00.000

--查询时间段定义

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

--结果

Item Date Opening IN IN_Retrun OUT OUT_Return Balance

---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------

aa 2005-02-01 100 180 0 55 0 225

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

（所影响的行数为 8 行）