按日统计,销售情况例子

/*--示例

--出处:邹建

在代码中，首先定义了一个最简单的出入库数据记录明细表（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 @#clearcase/" target="_blank" >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.0002           aa         90          1    2005-02-01 00:00:00.0003           aa         55          0    2005-02-01 00:00:00.0004           aa         -10         1    2005-02-02 00:00:00.0005           aa         -5          0    2005-02-03 00:00:00.0006           aa         200         1    2005-02-02 00:00:00.0007           aa         90          1    2005-02-01 00:00:00.0008           bb         95          1    2005-02-02 00:00:00.0009           bb         65          0    2005-02-03 00:00:00.00010          bb         -15         1    2005-02-05 00:00:00.00011          bb         -20         0    2005-02-05 00:00:00.00012          bb         100         1    2005-02-07 00:00:00.00013          cc         100         1    2005-01-07 00:00:00.000

--查询时间段定义DECLARE @dt1 datetime,@dt2 datetimeSELECT @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 aWHERE Date<@dt1 AND NOT EXISTS( SELECT * FROM tb WHERE Item=a.Item AND Date>@dt1 AND Date<DATEADD(Day,1,@dt2))GROUP BY ItemUNION 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 aWHERE Date>=@dt1 AND Date<DATEADD(Day,1,@dt2)GROUP BY CONVERT(char(10),Date,120),ItemORDER BY Item,Date

--结果

Item       Date       Opening     IN          IN_Retrun   OUT         OUT_Return  Balance     ---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- aa         2005-02-01 100         180         0           55          0           225aa         2005-02-02 225         200         10          0           0           415aa         2005-02-03 415         0           0           0           5           420bb         2005-02-02 0           95          0           0           0           95bb         2005-02-03 95          0           0           65          0           30bb         2005-02-05 30          0           15          0           20          35bb         2005-02-07 35          100         0           0           0           135cc         2005-02-01 100         0           0           0           0           100

（所影响的行数为 8 行）

原文转自：http://www.ltesting.net