34,593
社区成员
发帖
与我相关
我的任务
分享
FiscalYear FiscalMonth Dept CC Actual
FY1213 P1 IT 810 10000
FY1213 P2 IT 810 10000
FY1213 P3 IT 810 10000
FY1213 P4 IT 810 10000
FY1213 P5 IT 810 10000
FY1213 P1 AC 810 10000
FY1213 P2 AC 810 10000
FY1213 P3 AC 810 10000
FY1213 P4 AC 810 10000
FY1213 P5 AC 810 10000
FY1314 P1 IT 810 20000
FY1314 P2 IT 810 20000
FY1314 P3 IT 810 20000
FY1314 P4 IT 810 20000
FY1314 P5 IT 810 20000
FY1314 P1 AC 810 20000
FY1314 P2 AC 810 20000
FY1314 P3 AC 810 20000
FY1314 P4 AC 810 20000
FY1314 P5 AC 810 20000
Dept Actual Actual Last Y %
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-20 15:24:59
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([FiscalYear] varchar(6),[FiscalMonth] varchar(2),[Dept] varchar(2),[CC] int,[Actual] int)
insert [TB]
select 'FY1213','P1','IT',810,10000 union all
select 'FY1213','P2','IT',810,10000 union all
select 'FY1213','P3','IT',810,10000 union all
select 'FY1213','P4','IT',810,10000 union all
select 'FY1213','P5','IT',810,10000 union all
select 'FY1213','P1','AC',810,10000 union all
select 'FY1213','P2','AC',810,10000 union all
select 'FY1213','P3','AC',810,10000 union all
select 'FY1213','P4','AC',810,10000 union all
select 'FY1213','P5','AC',810,10000 union all
select 'FY1314','P1','IT',810,20000 union all
select 'FY1314','P2','IT',810,20000 union all
select 'FY1314','P3','IT',810,20000 union all
select 'FY1314','P4','IT',810,20000 union all
select 'FY1314','P5','IT',810,20000 union ALL
select 'FY1314','P1','AC',810,20000 union all
select 'FY1314','P2','AC',810,20000 union all
select 'FY1314','P3','AC',810,20000 union all
select 'FY1314','P4','AC',810,20000 union all
select 'FY1314','P5','AC',810,20000
--------------开始查询--------------------------
select dept,
sum(case when FiscalYear ='FY1314 ' and FiscalMonth <'P5' then Actual else 0 end) as Actual,
sum(case when FiscalYear ='FY1213 ' and FiscalMonth <'P5' then Actual else 0 end) as [Actual Last Y],
sum(case when FiscalYear ='FY1213 ' and FiscalMonth <'P5' then Actual else 0 end) *100.0/sum(case when FiscalYear ='FY1314 ' and FiscalMonth <'P5' then Actual else 0 end) as [%]
from
tb
group by
dept
----------------结果----------------------------
/*
dept Actual Actual Last Y %
---- ----------- ------------- ---------------------------------------
AC 80000 40000 50.000000000000
IT 80000 40000 50.000000000000
(2 行受影响)
*/
select dept,
sum(case when FiscalYear ='FY1314 ' and FiscalMonth ='P5' then Actual else 0 end) as Actual,
sum(case when FiscalYear ='FY1213 ' and FiscalMonth ='P5' then Actual else 0 end) as [Actual Last Y],
sum(case when FiscalYear ='FY1314 ' and FiscalMonth ='P5' then Actual else 0 end) *1.0/sum(case when FiscalYear ='FY1213 ' and FiscalMonth ='P5' then Actual else 0 end) as [%]
from
tb
group by
dept
select sum(Actual) Actual from tbl
where FiscalMonth<'P5'
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-20 15:24:59
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([FiscalYear] varchar(6),[FiscalMonth] varchar(2),[Dept] varchar(2),[CC] int,[Actual] int)
insert [TB]
select 'FY1213','P1','IT',810,10000 union all
select 'FY1213','P2','IT',810,10000 union all
select 'FY1213','P3','IT',810,10000 union all
select 'FY1213','P4','IT',810,10000 union all
select 'FY1213','P5','IT',810,10000 union all
select 'FY1213','P1','AC',810,10000 union all
select 'FY1213','P2','AC',810,10000 union all
select 'FY1213','P3','AC',810,10000 union all
select 'FY1213','P4','AC',810,10000 union all
select 'FY1213','P5','AC',810,10000 union all
select 'FY1314','P1','IT',810,20000 union all
select 'FY1314','P2','IT',810,20000 union all
select 'FY1314','P3','IT',810,20000 union all
select 'FY1314','P4','IT',810,20000 union all
select 'FY1314','P5','IT',810,20000 union ALL
select 'FY1314','P1','AC',810,20000 union all
select 'FY1314','P2','AC',810,20000 union all
select 'FY1314','P3','AC',810,20000 union all
select 'FY1314','P4','AC',810,20000 union all
select 'FY1314','P5','AC',810,20000
--------------开始查询--------------------------
SELECT ISNULL(b.dept,a.dept)dept,b.Actual,a.[Actual Last Year],CAST(CAST(([Actual Last Year]*1.0/Actual)*100 AS DECIMAL(5,2))AS VARCHAR(5))+'%' [%]
FROM (
select DEPT,SUM([Actual])[Actual Last Year] from [TB]
WHERE fiscalyear='fy1213'AND FiscalMonth <'P5'
GROUP BY DEpt)a RIGHT JOIN (
select dept,SUM([Actual])[Actual]
from [TB]
WHERE fiscalyear='fy1314' AND FiscalMonth <'P5'
GROUP BY DEpt) b ON a.dept=b.dept
----------------结果----------------------------
/*
dept Actual Actual Last Year %
---- ----------- ---------------- ------
AC 80000 40000 50.00%
IT 80000 40000 50.00%
*/