string sql2 = "select distinct a.* from (select * from v_TiShi where year(chaobiaodate)=year(getdate()) and month(chaobiaodate)=month(getdate())) as a, (select * from v_TiShi where year(chaobiaodate)=year(getdate()) and month(chaobiaodate)=month(getdate())-1) as b, (select * from v_TiShi where year(chaobiaodate)=year(getdate()) and month(chaobiaodate)=month(getdate())-2) as c where abs((a.zhishu-b.zhishu)-(b.zhishu-c.zhishu))>200 ";
实现的功能是：求出本月与上月某个值相比。落差大于200的所有信息
实现的功能是：求出本月与上月某个值相比。落差大于200的所有信息
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%
--*/