按日統計,銷售情況例子

/*--示例

--出處:鄒建

在代碼中，首先定義了一個最簡單的出入庫數據記錄明細表（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 行）