用SQL进行同比与环比查询

表结构如下:
ID  DepartName(部门) Sales（销售量) SalesDate(销售日期)
1      营销一部       300              2006-7-1 
2 营销二部       500              2006-7-1 
3 营销三部       800              2006-8-1 
4 营销一部       600              2006-8-1 
5 营销二部       800              2006-8-1 
6 营销一部       400              2007-7-1 
7 营销二部       800              2007-7-1 
8 营销三部       700              2007-8-1 
9 营销一部       600              2008-7-1 
10 营销二部       300              2008-7-1 
要根据要求得到以下数据
1）选择开始年月,结束年月，得到同比数据
eg.开始年月:2006-7 结束:2006-8
获取：
对比年月 DepartName(部门) Sales（销售总量)  上期  差异 差异率
2006-7    营销一部          300            0    300  无穷大
2006-7    营销一部          500            0    500  无穷大
2006-7    营销三部     0              0    0    0
2006-8 .... ....
(2)选择月份 获取环比数据
eg.选择 2008.7
部门    本月销售总量  去年同期  变动  变动率
营销一部  600              0      0      0
营销二部  300            800    -500  (300-800)/800

Code
 1--雇员数据
 2CREATE TABLE Employee(
 3ID int,              --雇员编号(主键)
 4Name nvarchar(10),   --雇员名称
 5Dept nvarchar(10))   --所属部门
 6INSERT Employee SELECT 1,N'张三',N'大客户部'
 7UNION  ALL      SELECT 2,N'李四',N'大客户部'
 8UNION  ALL      SELECT 3,N'王五',N'销售一部'
 9--费用表
10CREATE TABLE Expenses(
11EmployeeID int,        --雇员编号
12Date Datetime,         --发生日期
13Expenses nvarchar(10), --指标名称
14[Money] decimal(10,2)) --发生金额
15INSERT Expenses SELECT 1,'2004-01-01',N'销售',100
16UNION  ALL      SELECT 1,'2004-01-02',N'销售',150
17UNION  ALL      SELECT 1,'2004-12-01',N'销售',200
18UNION  ALL      SELECT 1,'2005-01-10',N'销售', 80
19UNION  ALL      SELECT 1,'2005-01-15',N'销售', 90
20UNION  ALL      SELECT 1,'2005-01-21',N'成本',  8
21UNION  ALL      SELECT 2,'2004-12-01',N'成本',  2
22UNION  ALL      SELECT 2,'2005-01-10',N'销售', 10
23UNION  ALL      SELECT 2,'2005-01-15',N'销售', 40
24UNION  ALL      SELECT 2,'2005-01-21',N'成本',  8
25UNION  ALL      SELECT 3,'2004-01-01',N'销售',200
26UNION  ALL      SELECT 3,'2004-12-10',N'销售', 80
27UNION  ALL      SELECT 3,'2005-01-15',N'销售', 90
28UNION  ALL      SELECT 3,'2005-01-21',N'销售',  8
29GO
30
31--统计
32DECLARE @Period char(6)
33SET @Period='200501' --统计的年月
34
35--统计处理
36DECLARE @Last_Period char(6),@Previous_Period char(6)
37SELECT @Last_Period=CONVERT(char(6),DATEADD(Year,-1,@Period+'01'),112),
38    @Previous_Period=CONVERT(char(6),DATEADD(Month,-1,@Period+'01'),112)
39SELECT Dept,Expenses,Name,
40    C_Money,
41    L_Money,
42    L_UP=C_Money-L_Money,
43    L_Prec=CASE
44            WHEN L_Money=0 THEN '----'
45            ELSE SUBSTRING('↓－↑',CAST(SIGN(C_Money-L_Money) as int)+2,1)
46                +CAST(CAST(ABS(C_Money-L_Money)*100/P_Money as decimal(10,2)) as varchar)+'%'
47        END,
48    P_Money,
49    P_UP=C_Money-P_Money,
50    P_Prec=CASE
51            WHEN P_Money=0 THEN '----'
52            ELSE SUBSTRING('↓－↑',CAST(SIGN(C_Money-P_Money) as int)+2,1)
53                +CAST(CAST(ABS(C_Money-P_Money)*100/P_Money as decimal(10,2)) as varchar)+'%'
54        END
55FROM(
56    SELECT a.Dept,b.Expenses,
57        Name=CASE WHEN GROUPING(Name)=1 THEN '<合计>' ELSE a.Name END,    
58        C_Money=ISNULL(SUM(CASE CONVERT(char(6),b.Date,112) WHEN @Period THEN b.[Money] END),0),
59        L_Money=ISNULL(SUM(CASE CONVERT(char(6),b.Date,112) WHEN @Last_Period THEN b.[Money] END),0),
60        P_Money=ISNULL(SUM(CASE CONVERT(char(6),b.Date,112) WHEN @Previous_Period THEN b.[Money] END),0)
61    FROM Employee a,Expenses b
62    WHERE a.ID=b.EmployeeID
63        AND CONVERT(char(6),b.Date,112) IN(@Last_Period,@Previous_Period,@Period)
64    GROUP BY a.Dept,b.Expenses,a.ID,a.Name WITH ROLLUP
65    HAVING (GROUPING(a.Name)=0 OR GROUPING(a.ID)=1)
66        AND (GROUPING(a.ID)=0 OR GROUPING(b.Expenses)=0))a
67
68/**//*--结果
69Dept     Expenses    Name    C_Money L_Money L_UP   L_Prec    P_Money P_UP   P_Prec 
70------- --------- -------- --------- -------- -------- -------- -------- -------- ------ 
71大客户部   成本   张三        8.00       .00      8.00      ----         .00         8.00         ----
72大客户部   成本   李四        8.00       .00      8.00      ----         2.00        6.00         ↑300.00%
73大客户部   成本   <合计>      16.00      .00      16.00     ----        2.00        14.00       ↑700.00%
74大客户部   销售   张三         170.00      250.00  -80.00   ↓32.00%     200.00      -30.00      ↓15.00%
75大客户部   销售   李四         50.00       .00      50.00     ----        .00         50.00        ----
76大客户部   销售   <合计>       220.00      250.00  -30.00   ↓12.00%      200.00      20.00       ↑10.00%
77销售一部   销售   王五         98.00       200.00  -102.00  ↓51.00%      80.00       18.00       ↑22.50%
78销售一部   销售   <合计>       98.00       200.00  -102.00  ↓51.00%      80.00       18.00       ↑22.50%
79--*/
80
81
82

转载于:https://www.cnblogs.com/ChuttySonic/archive/2009/08/19/1550199.html