SQL报表同比与环比查询
在做报表时用到SQL同比与环比查询。 先来看下表结构如下:
要根据要求得到以下数据
1）选择开始年月,结束年月，得到同比数据 eg.开始年月:2006-7 结束:2006-8 获取：
(2)选择月份获取环比数据 eg.选择 2008.7
--雇员数据
CREATE TABLE Employee(
ID int, --雇员编号(主键) Name nvarchar(10), --雇员名称 Deptnvarchar(10)) --所属部门
INSERT Employee SELECT 1,N'张三',N'大客户部' UNION ALL SELECT 2,N'李四',N'大客户部' UNION ALL SELECT 3,N'王五',N'销售一部'
--费用表
CREATE TABLE Expenses(
EmployeeIDint, --雇员编号 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_Periodchar(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 /**//*--结果