同期及上期数据对比处理示例.sql

--雇员数据
CREATE TABLE Employee(
ID int,              --雇员编号(主键)
Name nvarchar(10),   --雇员名称
Dept nvarchar(10))   --所属部门
INSERT Employee SELECT 1,N'张三',N'大客户部'
UNION ALL      SELECT 2,N'李四',N'大客户部'
UNION ALL      SELECT 3,N'王五',N'销售一部'
--费用表
CREATE TABLE Expenses(
EmployeeID int,        --雇员编号
Date Datetime,         --发生日期
Expenses nvarchar(10), --指标名称
[Money] decimal(10,2)) --发生金额
INSERT Expenses SELECT 1,'2004-01-01',N'销售',100
UNION ALL      SELECT 1,'2004-01-02',N'销售',150
UNION ALL      SELECT 1,'2004-12-01',N'销售',200
UNION ALL      SELECT 1,'2005-01-10',N'销售', 80
UNION ALL      SELECT 1,'2005-01-15',N'销售', 90
UNION ALL      SELECT 1,'2005-01-21',N'成本', 8
UNION ALL      SELECT 2,'2004-12-01',N'成本', 2
UNION ALL      SELECT 2,'2005-01-10',N'销售', 10
UNION ALL      SELECT 2,'2005-01-15',N'销售', 40
UNION ALL      SELECT 2,'2005-01-21',N'成本', 8
UNION ALL      SELECT 3,'2004-01-01',N'销售',200
UNION ALL      SELECT 3,'2004-12-10',N'销售', 80
UNION ALL      SELECT 3,'2005-01-15',N'销售', 90
UNION ALL      SELECT 3,'2005-01-21',N'销售', 8
GO

--统计
DECLARE @Period char(6)
SET @Period='200501' --统计的年月

--统计处理
DECLARE @Last_Period char(6),@Previous_Period char(6)
SELECT @Last_Period=CONVERT(char(6),DATEADD(Year,-1,@Period+'01'),112),
   @Previous_Period=CONVERT(char(6),DATEADD(Month,-1,@Period+'01'),112)
SELECT Dept,Expenses,Name,
   C_Money,
   L_Money,
   L_UP=C_Money-L_Money,
   L_Prec=CASE
           WHEN L_Money=0 THEN '----'
           ELSE SUBSTRING('↓－↑',CAST(SIGN(C_Money-L_Money) as int)+2,1)
               +CAST(CAST(ABS(C_Money-L_Money)*100/P_Money as decimal(10,2)) as varchar)+'%'
       END,
   P_Money,
   P_UP=C_Money-P_Money,
   P_Prec=CASE
           WHEN P_Money=0 THEN '----'
           ELSE SUBSTRING('↓－↑',CAST(SIGN(C_Money-P_Money) as int)+2,1)
               +CAST(CAST(ABS(C_Money-P_Money)*100/P_Money as decimal(10,2)) as varchar)+'%'
       END
FROM(
   SELECT a.Dept,b.Expenses,
       Name=CASE WHEN GROUPING(Name)=1 THEN '<合计>' ELSE a.Name END,
       C_Money=ISNULL(SUM(CASE CONVERT(char(6),b.Date,112) WHEN @Period THEN b.[Money] END),0),
       L_Money=ISNULL(SUM(CASE CONVERT(char(6),b.Date,112) WHEN @Last_Period THEN b.[Money] END),0),
       P_Money=ISNULL(SUM(CASE CONVERT(char(6),b.Date,112) WHEN @Previous_Period THEN b.[Money] END),0)
   FROM Employee a,Expenses b
   WHERE a.ID=b.EmployeeID
       AND CONVERT(char(6),b.Date,112) IN(@Last_Period,@Previous_Period,@Period)
   GROUP BY a.Dept,b.Expenses,a.ID,a.Name WITH ROLLUP
   HAVING (GROUPING(a.Name)=0 OR GROUPING(a.ID)=1)
       AND (GROUPING(a.ID)=0 OR GROUPING(b.Expenses)=0))a
/*--结果
Dept     Expenses    Name    C_Money L_Money L_UP   L_Prec    P_Money P_UP   P_Prec
------- --------- -------- --------- -------- -------- -------- -------- -------- ------
大客户部   成本   张三       8.00     .00      8.00      ----       .00        8.00        ----
大客户部   成本   李四       8.00     .00      8.00      ----       2.00       6.00        ↑300.00%
大客户部   成本   <合计>     16.00     .00      16.00     ----     2.00       14.00     ↑700.00%
大客户部   销售   张三        170.00     250.00 -80.00   ↓32.00%   200.00     -30.00     ↓15.00%
大客户部   销售   李四        50.00     .00      50.00     ----     .00        50.00       ----
大客户部   销售   <合计>     220.00     250.00 -30.00   ↓12.00%    200.00     20.00     ↑10.00%
销售一部   销售   王五        98.00     200.00 -102.00 ↓51.00%    80.00     18.00     ↑22.50%
销售一部   销售   <合计>     98.00     200.00 -102.00 ↓51.00%    80.00     18.00     ↑22.50%
--*/

chaunceyhao