114.同期及上期数据对比处理-案例

我就是我 2024-02-19 19:02 19阅读 0赞

--雇员数据
    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%
    --*/