经典存储过程计算报表
用单一存储过程计算报表，替换以前用java+Hibernate的给客户做的计算报表，客户每次用后系统慢等各种问题，该存储过程是当时花了近一周时间，看以前的逻辑，修改的，一开始觉得很简单，但越做越复杂，中途都想放弃，最终坚持下来，完整的替换了以前程序单条SQL计算，保留下来脚本，做纪念！
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getbetweenDays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[getbetweenDays]
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getDay]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[getDay]
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getDays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[getDays]
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hjtj]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[hjtj]
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hjtjold]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[hjtjold]
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 2008年7月9日9:30:49
-- Description: 比较两个时间段间交叉的天数
-- 判断(begin1,end1)时间段在(@begin2,@end2)中有几天（包含当天)
-- 调用[dbo].[getDays]('2008-06-06','2008-07-06','2008-07-01','2008-07-09')
-- 意思是:2008-06-06到2008-07-06中在'2008-07-01'到'2008-07-09'有几天
-- =============================================
create function [dbo].[getbetweenDays]
(@begin1 varchar(12),@end1 varchar(12),@begin2 varchar(12),@end2 varchar(12),@begin3 varchar(12),@end3 varchar(12))
returns int
as
begin
declare @num as int;
declare @beginDate as datetime;
declare @endDate as datetime;
declare @beginDate1 as datetime;
declare @endDate1 as datetime;
declare @beginDate2 as datetime;
declare @endDate2 as datetime;
set @beginDate=cast(@begin1 as datetime);
set @endDate=cast(@end1 as datetime);
set @beginDate1=cast(@begin2 as datetime);
set @endDate1=cast(@end2 as datetime);
set @beginDate2=cast(@begin3 as datetime);
set @endDate2=cast(@end3 as datetime);
set @num=0;
--两个时间交集
if(@begindate2<=@begindate1 and @enddate2<=@endDate1)
set @num=datediff(day,@begindate,@enddate)+1;
return @num;
end
go
-- =============================================
-- Author:
-- Create date: 2008年7月9日9:30:49
-- Description: 比较两个字符串间的天数
-- 判断(@begin时间在(@end)中有几天（包含当天)
-- 调用[dbo].[getDays]('2008-06-06','2008-07-06')
-- 意思是:2008-06-06到2008-07-06有几天
-- =============================================
CREATE function [dbo].[getDay](
@begin varchar(12),
@end varchar(12)
)returns int
begin
declare @num as int
set @num=0;
declare @beginDate as datetime;
declare @endDate as datetime;
if(@begin is null and @end is null)
return 100000000;
if(@begin>@end)
return 100000000;
set @beginDate=cast(@begin as datetime)
set @endDate=cast(@end as datetime)
set @num=datediff(day,@begindate,@enddate)+1;
return @num;
end
GO
-- =============================================
-- Author:
-- Create date: 2008年7月9日9:30:49
-- Description: 比较两个时间段间交叉的天数
-- 判断(begin1,end1)时间段在(@begin2,@end2)中有几天（包含当天)
-- 调用[dbo].[getDays]('2008-06-06','2008-07-06','2008-07-01','2008-07-09')
-- 意思是:2008-06-06到2008-07-06中在'2008-07-01'到'2008-07-09'有几天
-- =============================================
CREATE function [dbo].[getDays]
(@begin1 varchar(12),@end1 varchar(12),@begin2 varchar(12),@end2 varchar(12))
returns int
as
begin
declare @num as int;
declare @beginDate as datetime;
declare @endDate as datetime;
declare @beginDate1 as datetime;
declare @endDate1 as datetime;
set @beginDate=cast(@begin1 as datetime);
set @endDate=cast(@end1 as datetime);
set @beginDate1=cast(@begin2 as datetime);
set @endDate1=cast(@end2 as datetime);
set @num=0;
--如果两个时间差没有交集
if((@begindate<@begindate1 and @enddate<@begindate1) or (@begindate>@enddate1))
begin
return @num;
end;
--结束时间大于开始时间
if(@enddate>=@begindate1 and @begindate<=@begindate1)
set @num=datediff(day,@begindate1,@enddate)+1;
--时间在另个时间的内e
if(@beginDate>=@begindate1 and @enddate<=@enddate1)
set @num=datediff(day,@begindate,@enddate)+1;
--开始时间大于开始时间，结束时间大于结束时间
if(@begindate>@begindate1 and @enddate>@enddate1 and @begindate<=@enddate1)
set @num=datediff(day,@begindate,@enddate1)+1;
if(@begindate<=@begindate1 and @enddate>=@enddate1 and @begindate1<= @enddate)
set @num=datediff(day,@begindate1,@enddate1)+1;
return @num;
end
Go
GO
CREATE proc [dbo].[hjtj](
@orgid varchar(32),
@begindate varchar(12),
@enddate varchar(12)
)
as
set nocount on;
--办事处临时表
create table #OrgOffice(
id char(32),
)
--主计划临时表
create table #mainplan(
id char(32)
)
--类型临时表
create table #type(
id char(32)
);
create table #tb(
typename varchar(30),
totalfee numeric(18,3),
months varchar(32),
totalamount numeric(20,3)
)
--取出该类型的全部子类型(包括自己)
/*
insert into #type(id,pid,typeName)
select id,pid,'终端类型' from selectitem where pid='402881e80caa9192010caa971be5000c'
union all
union all
select id,pid,'非终端类型' from selectitem a where a.pid='402881e80caa9192010caa97560f000e'
union all
select id,pid,'地面广告宣传 ' from selectitem a where a.pid='402881e80caa9192010caa9806450010'
union all
select id,pid,'周边开发 ' from selectitem a where a.pid='402881e80caa9192010caa9989c80015'
union all
select id,id,'总部宣传品 ' from selectitem a where a.id='402881e80caa9192010caa9868b80012'
*/
insert into #type(id)
select id from selectitem a where a.pid='402881e80caa9192010caa971be5000c'
or a.typeid='402881e80caa9192010caa96a85a000a'
or a.pid='402881e80caa9192010caa97560f000e'
or a.pid='402881e80caa9192010caa9806450010'
or a.pid='402881e80caa9192010caa9989c80015'
or a.id='402881e80caa9192010caa9868b80012'
--递归查询全部下属机构
;
with org(orgid)
as
(
select a.oid from orgunitlink a
where a.pid=@orgid
union all
select c.oid
from orgunitlink c inner join org aa
on aa.orgid=c.pid
)
insert into #OrgOffice(id) select a.orgid from org a union select @orgid
--插入机构下的主计划
insert into #mainplan
select a.id from Mfm_Mainplaninfo a inner join #orgoffice b on b.id=a.orgunitid
declare @beginmonth as datetime;
declare @endmonth as datetime;
declare @beginmonth1 as varchar(10)
declare @endmonth1 as varchar(10)
set @beginmonth=cast(@begindate as datetime);
set @endmonth=cast(@enddate as datetime);
while @beginmonth<=@endmonth
begin
set @beginmonth1=convert(varchar(10),@beginmonth,120)
set @endmonth1=convert(varchar(10),@endmonth,120)
if(dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime))>=@enddate)--结束时间在小于当月最后一天
begin
--终端促销日计划金额,日任务量
insert into #tb(typename,totalfee,months,totalamount)
select '终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from Mfm_terminalplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=1 and planstatus=2
and not exists (select 'X' from delobj delobj1_ where Mfm_terminalplan.id=delobj1_.objid)
union all
--非终端日计划金额,日任务量
select '非终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from Mfm_Noterminalplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=1 and planstatus=2
union all
--地面广告宣传日计划金额,日任务量
select '地面宣传活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from Mfm_Groundplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=1 and planstatus=2
union all
--周边开发
select '周边开发活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from Mfm_Areadevplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=1 and planstatus=2
union all
--总部宣传品
select '总部宣传品活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from Mfm_Groupmaterialplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=1 and planstatus=2
--媒体类型
union all
select at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mm
on mm.id = a1.mediaplanid
cross join selectitem at
where a1.mediaplanid in(
select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo md
on m.id = md.mediaplanid
inner join selectitem a
on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeid
inner join #mainplan c
on m.mainplanid =c.id
where md.begindate <= @endmonth1 and
md.enddate >=@beginmonth1
and md.begindate<=md.enddate
and md.mark=1)
and a1.mark=1
and at.id=mm.mediatypeid
and at.typeid='402881e80caa9192010caa96a85a000a'
group by at.objname
insert into #tb(typename,totalfee,months)
select a.objname,0,convert(varchar(7),@beginmonth,120) from
selectitem a where a.typeid='402881e80caa9192010caa96a85a000a' and
not exists (select 1 from #tb t where t.typename=a.objname
and t.months=convert(varchar(7),@beginmonth,120))
set @beginmonth=dateadd(month,3,@endmonth);--设置时间大于结束时间，循环结束
end
else --结束时间在大于当月最后一天
begin
--set @beginmonth=@begindate;
set @endmonth=dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime));--本月的最后一天
if(@endmonth>=@enddate)
set @endmonth=@enddate;--假如下一个月最后一天大于结束时间
set @beginmonth1=convert(varchar(10),@beginmonth,120)
set @endmonth1=convert(varchar(10),@endmonth,120)
--运行计算
insert into #tb(typename,totalfee,months,totalamount)
select '终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from Mfm_terminalplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=1 and planstatus=2
and not exists (select 'X' from delobj delobj1_ where Mfm_terminalplan.id=delobj1_.objid)
union all
--非终端日计划金额,日任务量
select '非终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from Mfm_Noterminalplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=1 and planstatus=2
union all
--地面广告宣传日计划金额,日任务量
select '地面宣传活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from Mfm_Groundplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=1 and planstatus=2
union all
--周边开发
select '周边开发活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from Mfm_Areadevplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=1 and planstatus=2
union all
--总部宣传品
select '总部宣传品活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from Mfm_Groupmaterialplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=1 and planstatus=2
--媒体类型
union all
select at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))
from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mm
on mm.id = a1.mediaplanid
cross join selectitem at
where a1.mediaplanid in(
select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo md
on m.id = md.mediaplanid
inner join selectitem a
on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeid
inner join #mainplan c
on m.mainplanid =c.id
where md.begindate <= @endmonth1 and
md.enddate >=@beginmonth1
and md.begindate<=md.enddate
and md.mark=1)
and a1.mark=1
and at.id=mm.mediatypeid
and at.typeid='402881e80caa9192010caa96a85a000a'
group by at.objname
--select @beginmonth--2008-05-01 00:00:00.000
--select @endmonth
insert into #tb(typename,totalfee,months)
select a.objname,0,convert(varchar(7),@beginmonth,120) from selectitem a where a.typeid='402881e80caa9192010caa96a85a000a' and
not exists (select 1 from #tb t where t.typename=a.objname
and t.months=convert(varchar(7),@beginmonth,120))
--计算完毕后,
set @beginmonth=cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime);--开始时间为下个月的第一天
set @endmonth=@enddate
end
end
--总投入
insert into #tb(typename,totalfee,months)
select typename,sum(totalfee),'总投入'
from #tb group by typename
--计算任务调整
insert into #tb(typename,totalfee,months)
select a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406
inner join selectitem a on a.id=field005
where field011='4028827015f58e7901161d0b63236364'
and field012='40288141103870fb0110487902250403'
and field005
in ('402881e80caa9192010caa971be5000c' --终端类型
, '402881e80caa9192010caa97560f000e' --非终端类型
,'402881e80caa9192010caa9806450010' --地面广告宣传
,'402881e80caa9192010caa9989c80015' --周边开发
,'402881e80caa9192010caa9868b80012'--总部宣传品
)
and field018='1'
and field002 in (select id from #orgoffice)
group by a.objname
union all
select a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406
inner join selectitem a on a.typeid='402881e80caa9192010caa96a85a000a'
and a.id=field025
where
field011='4028827015f58e7901161d0b63236364'
and field012='40288141103870fb0110487902250402'
and field018='1'
and field002 in (select id from #orgoffice )
group by a.objname--通过媒体类型聚合
--预留额度（机动费）
insert into #tb(typename,totalfee,months)
select ks.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget a
inner join #mainplan b on b.id=a.mainplanid
inner join
(select top 1 * from Mfm_Assessinfo where
dbo.getdays(@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.id
inner join selectitem k on k.id=a.acttypeid
and (k.pid='402881e80caa9192010caa97560f000e' or k.pid='402881e80caa9192010caa971be5000c'
or k.pid='402881e80caa9192010caa9806450010'
or k.pid='402881e80caa9192010caa9989c80015')
inner join selectitem ks on ks.id=k.pid
and isactive='1'
group by ks.objname
union all --电视媒体,总部宣传品
select k.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget a
inner join #mainplan b on b.id=a.mainplanid
inner join
(select top 1 * from Mfm_Assessinfo where
dbo.getdays(@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.id
inner join selectitem k on k.id=a.acttypeid and (k.typeid='402881e80caa9192010caa96a85a000a'
or k.id='402881e80caa9192010caa9868b80012')
and isactive='1'
group by k.objname
--插入没有数据的机动费，默认设置为0
insert into #tb(typename,totalfee,months)
select a.objname,0,'预留机动费' from selectitem a
where a.id='402881e80caa9192010caa971be5000c'
or a.typeid='402881e80caa9192010caa96a85a000a'
or a.id='402881e80caa9192010caa97560f000e'
or a.id='402881e80caa9192010caa9806450010'
or a.id='402881e80caa9192010caa9989c80015'
or a.id='402881e80caa9192010caa9868b80012'
and not exists(select * from #tb c where c.months='预留机动费' and a.objname=c.typename)
--插入总投入
insert into #tb(typename,totalfee,months)
select typename,sum(totalamount),'总任务额度'
from #tb where totalamount>0
group by typename
--加上预留机动费
update #tb set totalfee=totalfee+c
from #tb inner join
(select a.typename, sum(totalfee) c from #tb a
where a.months='预留机动费'
group by a.typename) c
on c.typename=#tb.typename
where #tb.months='总任务额度'
--加上任务调整
update #tb set totalfee=totalfee+c
from #tb inner join
(select a.typename, sum(totalfee) c from #tb a
where a.months='任务调整'
group by a.typename) c
on c.typename=#tb.typename
where #tb.months='总任务额度'
declare @sql as varchar(4000);
set @sql='select typename [活动类型]'
select @sql=@sql+',isnull(max(case when months='''+months+''' then totalfee end),0) ['+months+']' from (select distinct months from #tb ) b
set @sql=@sql+' from #tb t group by typename order by reverse(typename) desc';
print @sql
exec(@sql)
go
/****** 对象: StoredProcedure [dbo].[hjtjOrigin1] 脚本日期: 07/09/2008 13:54:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[hjtjold](
@orgid varchar(32),
@begindate varchar(12),
@enddate varchar(12)
)
as
set nocount on;
--办事处临时表
create table #OrgOffice(
id char(32),
company char(32)
)
--主计划临时表
create table #mainplan(
id char(32)
)
--类型临时表
create table #type(
id char(32)
--pid varchar(32),
--typeName varchar(32) --上级类型的名称，如(非终端,周边开发)
);
create table #tb(
typename varchar(30),
totalfee numeric(18,3),
months varchar(32),
totalamount numeric(20,3)
)
declare @regionS as varchar(500);
--片区
set @regions='402881ba0d6777c5010d68a41ee70037,402881ba0d6777c5010d68a7432f003b,402881ba0d6777c5010d68ab52c30047,402881ba0d6777c5010d68abcc94004a,402881ba0d6777c5010d68ac318b004d'
--取出该类型的全部子类型(包括自己)
/*
insert into #type(id,pid,typeName)
select id,pid,'终端类型' from selectitem where pid='402881e80caa9192010caa971be5000c'
union all
union all
select id,pid,'非终端类型' from selectitem a where a.pid='402881e80caa9192010caa97560f000e'
union all
select id,pid,'地面广告宣传 ' from selectitem a where a.pid='402881e80caa9192010caa9806450010'
union all
select id,pid,'周边开发 ' from selectitem a where a.pid='402881e80caa9192010caa9989c80015'
union all
select id,id,'总部宣传品 ' from selectitem a where a.id='402881e80caa9192010caa9868b80012'
*/
insert into #type(id)
select id from selectitem a where a.pid='402881e80caa9192010caa971be5000c'
or a.typeid='402881e80caa9192010caa96a85a000a'
or a.pid='402881e80caa9192010caa97560f000e'
or a.pid='402881e80caa9192010caa9806450010'
or a.pid='402881e80caa9192010caa9989c80015'
or a.id='402881e80caa9192010caa9868b80012'
--递归查询全部下属机构
;
with org(orgid)
as
(
select a.oid from orgunitlink a
where a.pid=@orgid
union all
select c.oid
from orgunitlink c inner join org aa
on aa.orgid=c.pid
)
insert into #OrgOffice(id) select a.orgid from org a
--插入机构下的主计划
insert into #mainplan
select a.id from Mfm_Mainplaninfo a inner join #orgoffice b on b.id=a.orgunitid
declare @beginmonth as datetime;
declare @endmonth as datetime;
declare @beginmonth1 as varchar(10)
declare @endmonth1 as varchar(10)
set @beginmonth=cast(@begindate as datetime);
set @endmonth=cast(@enddate as datetime);
while @beginmonth<=@endmonth
begin
set @beginmonth1=convert(varchar(10),@beginmonth,120)
set @endmonth1=convert(varchar(10),@endmonth,120)
if(dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime))>=@enddate)--结束时间在小于当月最后一天
begin
--终端促销日计划金额,日任务量
insert into #tb(typename,totalfee,months,totalamount)
select '终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from Mfm_terminalplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=0
union all
--非终端日计划金额,日任务量
select '非终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from Mfm_Noterminalplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=0
union all
--地面广告宣传日计划金额,日任务量
select '地面宣传活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from Mfm_Groundplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=0
union all
--周边开发
select '周边开发活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from Mfm_Areadevplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=0
union all
--总部宣传品
select '总部宣传品活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from Mfm_Groupmaterialplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=0
--媒体类型
union all
select at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mm
on mm.id = a1.mediaplanid
cross join selectitem at
where a1.mediaplanid in(
select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo md
on m.id = md.mediaplanid
inner join selectitem a
on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeid
inner join #mainplan c
on m.mainplanid =c.id
where md.begindate <= @endmonth1 and
md.enddate >=@beginmonth1
and md.begindate<=md.enddate
and md.mark=0)
and a1.mark=0
and at.id=mm.mediatypeid
and at.typeid='402881e80caa9192010caa96a85a000a'
group by at.objname
--111 select * from #tb
--插入没有该媒体类型（电视，网络等)数据为0
insert into #tb(typename,totalfee,months)
select a.objname,0,convert(varchar(7),@beginmonth,120) from
selectitem a where a.typeid='402881e80caa9192010caa96a85a000a' and
not exists (select 1 from #tb t where t.typename=a.objname
and t.months=convert(varchar(7),@beginmonth,120))
set @beginmonth=dateadd(month,3,@endmonth);--设置时间大于结束时间，循环结束
end
else --结束时间在大于当月最后一天
begin
set @endmonth=dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime));--本月的最后一天
if(@endmonth>=@enddate)
set @endmonth=@enddate;--假如下一个月最后一天大于结束时间
set @beginmonth1=convert(varchar(10),@beginmonth,120)
set @endmonth1=convert(varchar(10),@endmonth,120)
--运行计算
insert into #tb(typename,totalfee,months,totalamount)
select '终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from Mfm_terminalplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=0
union all
--非终端日计划金额,日任务量
select '非终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from Mfm_Noterminalplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=0
union all
--地面广告宣传日计划金额,日任务量
select '地面宣传活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from Mfm_Groundplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=0
union all
--周边开发
select '周边开发活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from Mfm_Areadevplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=0
union all
--总部宣传品
select '总部宣传品活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from Mfm_Groupmaterialplan
inner join Mfm_Mainplaninfo a on mainplanid=a.id
inner join #orgoffice b on b.id=a.orgunitid
inner join #type on acttype=#type.id
inner join orgunit on orgunit.id=b.id
inner join selectitem k on k.id=#type.id
where
begindate<=@endmonth1
and enddate>=@beginmonth1
and begindate<=enddate
and mark=0
--媒体类型
union all
select at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入
,convert(varchar(7),@beginmonth,120)
,cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))
from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mm
on mm.id = a1.mediaplanid
cross join selectitem at
where a1.mediaplanid in(
select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo md
on m.id = md.mediaplanid
inner join selectitem a
on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeid
inner join #mainplan c
on m.mainplanid =c.id
where md.begindate <= @endmonth1 and
md.enddate >=@beginmonth1
and md.begindate<=md.enddate
and md.mark=0)
and a1.mark=0
and at.id=mm.mediatypeid
and at.typeid='402881e80caa9192010caa96a85a000a'
group by at.objname
--select * from #tb
insert into #tb(typename,totalfee,months)
select a.objname,0,convert(varchar(7),@beginmonth,120) from selectitem a where a.typeid='402881e80caa9192010caa96a85a000a' and
not exists (select 1 from #tb t where t.typename=a.objname
and t.months=convert(varchar(7),@beginmonth,120))
--计算完毕后,开始时间为下个月的第一天
set @beginmonth=cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime);
set @endmonth=@enddate --结束时间为默认结束时间
end
end
--计算总投入
insert into #tb(typename,totalfee,months)
select typename,sum(totalfee),'总投入'
from #tb group by typename
--任务调整
insert into #tb(typename,totalfee,months)
select a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406
inner join selectitem a on a.id=field005
where field011='4028827015f58e7901161d0b63236364'
and field012='40288141103870fb0110487902250403'
and field005
in ('402881e80caa9192010caa971be5000c' --终端类型
, '402881e80caa9192010caa97560f000e' --非终端类型
,'402881e80caa9192010caa9806450010' --地面广告宣传
,'402881e80caa9192010caa9989c80015' --周边开发
,'402881e80caa9192010caa9868b80012'--总部宣传品
)
and field018='1'
and field002 in (select id from #orgoffice)
and field027 is null --为null为原始计划
group by a.objname
union all
select a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406
inner join selectitem a on a.typeid='402881e80caa9192010caa96a85a000a'
and a.id=field025
where
field011='4028827015f58e7901161d0b63236364'
and field012='40288141103870fb0110487902250402'
and field018='1'
and field002 in (select id from #orgoffice )
and field027 is null --为null为原始计划
group by a.objname--通过媒体类型聚合
--预留额度（机动费）
insert into #tb(typename,totalfee,months)
select ks.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget a
inner join #mainplan b on b.id=a.mainplanid
inner join
(select top 1 * from Mfm_Assessinfo where
dbo.getdays(@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.id
inner join selectitem k on k.id=a.acttypeid
and (k.pid='402881e80caa9192010caa97560f000e' or k.pid='402881e80caa9192010caa971be5000c'
or k.pid='402881e80caa9192010caa9806450010'
or k.pid='402881e80caa9192010caa9989c80015')
inner join selectitem ks on ks.id=k.pid
and isactive='0'
group by ks.objname
union all --电视媒体,总部宣传品
select k.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget a
inner join #mainplan b on b.id=a.mainplanid
inner join
(select top 1 * from Mfm_Assessinfo where
dbo.getdays(@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.id
inner join selectitem k on k.id=a.acttypeid and (k.typeid='402881e80caa9192010caa96a85a000a'
or k.id='402881e80caa9192010caa9868b80012')
and isactive='0'
group by k.objname
--插入没有数据的机动费，默认设置为0
insert into #tb(typename,totalfee,months)
select a.objname,0,'预留机动费' from selectitem a
where a.id='402881e80caa9192010caa971be5000c'
or a.typeid='402881e80caa9192010caa96a85a000a'
or a.id='402881e80caa9192010caa97560f000e'
or a.id='402881e80caa9192010caa9806450010'
or a.id='402881e80caa9192010caa9989c80015'
or a.id='402881e80caa9192010caa9868b80012'
and not exists(select * from #tb c where c.months='预留机动费' and a.objname=c.typename)
--计算总任务额度(预留机动费+任务调整+总投入)
--插入总投入
insert into #tb(typename,totalfee,months)
select typename,sum(totalamount),'总任务额度'
from #tb where totalamount>0
group by typename
--加上预留机动费
update #tb set totalfee=totalfee+c
from #tb inner join
(select a.typename, sum(totalfee) c from #tb a
where a.months='预留机动费'
group by a.typename) c
on c.typename=#tb.typename
where #tb.months='总任务额度'
--加上任务调整
update #tb set totalfee=totalfee+c
from #tb inner join
(select a.typename, sum(totalfee) c from #tb a
where a.months='任务调整'
group by a.typename) c
on c.typename=#tb.typename
where #tb.months='总任务额度'
--输出数据
declare @sql as varchar(4000);
set @sql='select typename [活动类型]'
select @sql=@sql+',isnull(max(case when months='''+months+''' then totalfee end),0) ['+months+']' from (select distinct months from #tb ) b
set @sql=@sql+' from #tb t group by typename order by reverse(typename) desc';
print @sql
exec(@sql)
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getbetweenDays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[getbetweenDays]goIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getDay]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[getDay]goIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getDays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[getDays]goIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hjtj]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[hjtj]goIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hjtjold]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[hjtjold]goSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: -- Create date: 2008年7月9日9:30:49-- Description: 比较两个时间段间交叉的天数-- 判断(begin1,end1)时间段在(@begin2,@end2)中有几天（包含当天)-- 调用[dbo].[getDays]('2008-06-06','2008-07-06','2008-07-01','2008-07-09')-- 意思是:2008-06-06到2008-07-06中在'2008-07-01'到'2008-07-09'有几天-- =============================================create function [dbo].[getbetweenDays](@begin1 varchar(12),@end1 varchar(12),@begin2 varchar(12),@end2 varchar(12),@begin3 varchar(12),@end3 varchar(12))returns int as begin declare @num as int; declare @beginDate as datetime; declare @endDate as datetime; declare @beginDate1 as datetime; declare @endDate1 as datetime; declare @beginDate2 as datetime; declare @endDate2 as datetime; set @beginDate=cast(@begin1 as datetime); set @endDate=cast(@end1 as datetime); set @beginDate1=cast(@begin2 as datetime); set @endDate1=cast(@end2 as datetime); set @beginDate2=cast(@begin3 as datetime); set @endDate2=cast(@end3 as datetime); set @num=0; --两个时间交集 if(@begindate2<=@begindate1 and @enddate2<=@endDate1) set @num=datediff(day,@begindate,@enddate)+1; return @num;endgo-- =============================================-- Author: -- Create date: 2008年7月9日9:30:49-- Description: 比较两个字符串间的天数-- 判断(@begin时间在(@end)中有几天（包含当天)-- 调用[dbo].[getDays]('2008-06-06','2008-07-06')-- 意思是:2008-06-06到2008-07-06有几天-- =============================================CREATE function [dbo].[getDay]( @begin varchar(12), @end varchar(12))returns intbegin declare @num as int set @num=0; declare @beginDate as datetime; declare @endDate as datetime; if(@begin is null and @end is null) return 100000000; if(@begin>@end) return 100000000; set @beginDate=cast(@begin as datetime) set @endDate=cast(@end as datetime) set @num=datediff(day,@begindate,@enddate)+1; return @num;endGO-- =============================================-- Author: -- Create date: 2008年7月9日9:30:49-- Description: 比较两个时间段间交叉的天数-- 判断(begin1,end1)时间段在(@begin2,@end2)中有几天（包含当天)-- 调用[dbo].[getDays]('2008-06-06','2008-07-06','2008-07-01','2008-07-09')-- 意思是:2008-06-06到2008-07-06中在'2008-07-01'到'2008-07-09'有几天-- =============================================CREATE function [dbo].[getDays](@begin1 varchar(12),@end1 varchar(12),@begin2 varchar(12),@end2 varchar(12))returns intas begin declare @num as int; declare @beginDate as datetime; declare @endDate as datetime; declare @beginDate1 as datetime; declare @endDate1 as datetime; set @beginDate=cast(@begin1 as datetime); set @endDate=cast(@end1 as datetime); set @beginDate1=cast(@begin2 as datetime); set @endDate1=cast(@end2 as datetime); set @num=0; --如果两个时间差没有交集 if((@begindate<@begindate1 and @enddate<@begindate1) or (@begindate>@enddate1)) begin return @num; end; --结束时间大于开始时间 if(@enddate>=@begindate1 and @begindate<=@begindate1) set @num=datediff(day,@begindate1,@enddate)+1; --时间在另个时间的内e if(@beginDate>=@begindate1 and @enddate<=@enddate1) set @num=datediff(day,@begindate,@enddate)+1; --开始时间大于开始时间，结束时间大于结束时间 if(@begindate>@begindate1 and @enddate>@enddate1 and @begindate<=@enddate1) set @num=datediff(day,@begindate,@enddate1)+1; if(@begindate<=@begindate1 and @enddate>=@enddate1 and @begindate1<= @enddate) set @num=datediff(day,@begindate1,@enddate1)+1; return @num;endGoGOCREATE proc [dbo].[hjtj]( @orgid varchar(32), @begindate varchar(12), @enddate varchar(12))as set nocount on; --办事处临时表 create table #OrgOffice( id char(32), ) --主计划临时表 create table #mainplan( id char(32) ) --类型临时表 create table #type( id char(32) ); create table #tb( typename varchar(30), totalfee numeric(18,3), months varchar(32), totalamount numeric(20,3) ) --取出该类型的全部子类型(包括自己) /*
insert into #type(id,pid,typeName) select id,pid,'终端类型' from selectitem where pid='402881e80caa9192010caa971be5000c' union all union all select id,pid,'非终端类型' from selectitem a where a.pid='402881e80caa9192010caa97560f000e' union all select id,pid,'地面广告宣传 ' from selectitem a where a.pid='402881e80caa9192010caa9806450010' union all select id,pid,'周边开发 ' from selectitem a where a.pid='402881e80caa9192010caa9989c80015' union all select id,id,'总部宣传品 ' from selectitem a where a.id='402881e80caa9192010caa9868b80012' */insert into #type(id) select id from selectitem a where a.pid='402881e80caa9192010caa971be5000c' or a.typeid='402881e80caa9192010caa96a85a000a' or a.pid='402881e80caa9192010caa97560f000e' or a.pid='402881e80caa9192010caa9806450010' or a.pid='402881e80caa9192010caa9989c80015' or a.id='402881e80caa9192010caa9868b80012' --递归查询全部下属机构; with org(orgid) as ( select a.oid from orgunitlink a where a.pid=@orgid union all select c.oid from orgunitlink c inner join org aa on aa.orgid=c.pid ) insert into #OrgOffice(id) select a.orgid from org a union select @orgid --插入机构下的主计划 insert into #mainplan select a.id from Mfm_Mainplaninfo a inner join #orgoffice b on b.id=a.orgunitiddeclare @beginmonth as datetime;declare @endmonth as datetime;declare @beginmonth1 as varchar(10)declare @endmonth1 as varchar(10)set @beginmonth=cast(@begindate as datetime);set @endmonth=cast(@enddate as datetime);while @beginmonth<=@endmonthbegin set @beginmonth1=convert(varchar(10),@beginmonth,120) set @endmonth1=convert(varchar(10),@endmonth,120) if(dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime))>=@enddate)--结束时间在小于当月最后一天 begin --终端促销日计划金额,日任务量 insert into #tb(typename,totalfee,months,totalamount) select '终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) from Mfm_terminalplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=1 and planstatus=2 and not exists (select 'X' from delobj delobj1_ where Mfm_terminalplan.id=delobj1_.objid)union all --非终端日计划金额,日任务量select '非终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) from Mfm_Noterminalplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=1 and planstatus=2 union all--地面广告宣传日计划金额,日任务量select '地面宣传活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) from Mfm_Groundplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=1 and planstatus=2union all--周边开发select '周边开发活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) from Mfm_Areadevplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=1 and planstatus=2union all--总部宣传品 select '总部宣传品活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) from Mfm_Groupmaterialplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=1 and planstatus=2--媒体类型union allselect at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3))from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mmon mm.id = a1.mediaplanid cross join selectitem at where a1.mediaplanid in(select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo mdon m.id = md.mediaplanid inner join selectitem a on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeidinner join #mainplan con m.mainplanid =c.idwhere md.begindate <= @endmonth1 andmd.enddate >=@beginmonth1and md.begindate<=md.enddate and md.mark=1)and a1.mark=1and at.id=mm.mediatypeidand at.typeid='402881e80caa9192010caa96a85a000a'group by at.objnameinsert into #tb(typename,totalfee,months)select a.objname,0,convert(varchar(7),@beginmonth,120) from selectitem a where a.typeid='402881e80caa9192010caa96a85a000a' andnot exists (select 1 from #tb t where t.typename=a.objname and t.months=convert(varchar(7),@beginmonth,120))set @beginmonth=dateadd(month,3,@endmonth);--设置时间大于结束时间，循环结束endelse --结束时间在大于当月最后一天begin --set @beginmonth=@begindate; set @endmonth=dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime));--本月的最后一天 if(@endmonth>=@enddate) set @endmonth=@enddate;--假如下一个月最后一天大于结束时间 set @beginmonth1=convert(varchar(10),@beginmonth,120) set @endmonth1=convert(varchar(10),@endmonth,120)--运行计算 insert into #tb(typename,totalfee,months,totalamount) select '终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) from Mfm_terminalplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=1 and planstatus=2 and not exists (select 'X' from delobj delobj1_ where Mfm_terminalplan.id=delobj1_.objid)union all --非终端日计划金额,日任务量select '非终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) from Mfm_Noterminalplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=1 and planstatus=2 union all--地面广告宣传日计划金额,日任务量select '地面宣传活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) from Mfm_Groundplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=1 and planstatus=2union all--周边开发select '周边开发活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) from Mfm_Areadevplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=1 and planstatus=2union all--总部宣传品 select '总部宣传品活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) from Mfm_Groupmaterialplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=1 and planstatus=2--媒体类型union allselect at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate)) as numeric(22,3)) from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mmon mm.id = a1.mediaplanid cross join selectitem at where a1.mediaplanid in(select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo mdon m.id = md.mediaplanid inner join selectitem a on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeidinner join #mainplan con m.mainplanid =c.idwhere md.begindate <= @endmonth1 andmd.enddate >=@beginmonth1and md.begindate<=md.enddate and md.mark=1)and a1.mark=1and at.id=mm.mediatypeidand at.typeid='402881e80caa9192010caa96a85a000a'group by at.objname--select @beginmonth--2008-05-01 00:00:00.000--select @endmonthinsert into #tb(typename,totalfee,months)select a.objname,0,convert(varchar(7),@beginmonth,120) from selectitem a where a.typeid='402881e80caa9192010caa96a85a000a' andnot exists (select 1 from #tb t where t.typename=a.objname and t.months=convert(varchar(7),@beginmonth,120))--计算完毕后, set @beginmonth=cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime);--开始时间为下个月的第一天 set @endmonth=@enddate endend --总投入insert into #tb(typename,totalfee,months)select typename,sum(totalfee),'总投入'from #tb group by typename--计算任务调整insert into #tb(typename,totalfee,months)select a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406 inner join selectitem a on a.id=field005where field011='4028827015f58e7901161d0b63236364'and field012='40288141103870fb0110487902250403'and field005in ('402881e80caa9192010caa971be5000c' --终端类型, '402881e80caa9192010caa97560f000e' --非终端类型,'402881e80caa9192010caa9806450010' --地面广告宣传,'402881e80caa9192010caa9989c80015' --周边开发,'402881e80caa9192010caa9868b80012'--总部宣传品)and field018='1' and field002 in (select id from #orgoffice)group by a.objnameunion allselect a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406 inner join selectitem a on a.typeid='402881e80caa9192010caa96a85a000a'and a.id=field025where field011='4028827015f58e7901161d0b63236364'and field012='40288141103870fb0110487902250402'and field018='1' and field002 in (select id from #orgoffice )group by a.objname--通过媒体类型聚合--预留额度（机动费）insert into #tb(typename,totalfee,months)select ks.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget ainner join #mainplan b on b.id=a.mainplanidinner join (select top 1 * from Mfm_Assessinfo where dbo.getdays(@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.idinner join selectitem k on k.id=a.acttypeidand (k.pid='402881e80caa9192010caa97560f000e' or k.pid='402881e80caa9192010caa971be5000c' or k.pid='402881e80caa9192010caa9806450010' or k.pid='402881e80caa9192010caa9989c80015')inner join selectitem ks on ks.id=k.pidand isactive='1'group by ks.objnameunion all --电视媒体,总部宣传品select k.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget ainner join #mainplan b on b.id=a.mainplanid inner join (select top 1 * from Mfm_Assessinfo where dbo.getdays(@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.idinner join selectitem k on k.id=a.acttypeid and (k.typeid='402881e80caa9192010caa96a85a000a'or k.id='402881e80caa9192010caa9868b80012')and isactive='1'group by k.objname--插入没有数据的机动费，默认设置为0insert into #tb(typename,totalfee,months)select a.objname,0,'预留机动费' from selectitem a where a.id='402881e80caa9192010caa971be5000c' or a.typeid='402881e80caa9192010caa96a85a000a' or a.id='402881e80caa9192010caa97560f000e' or a.id='402881e80caa9192010caa9806450010' or a.id='402881e80caa9192010caa9989c80015' or a.id='402881e80caa9192010caa9868b80012' and not exists(select * from #tb c where c.months='预留机动费' and a.objname=c.typename)--插入总投入insert into #tb(typename,totalfee,months)select typename,sum(totalamount),'总任务额度'from #tb where totalamount>0 group by typename--加上预留机动费update #tb set totalfee=totalfee+cfrom #tb inner join (select a.typename, sum(totalfee) c from #tb awhere a.months='预留机动费' group by a.typename) con c.typename=#tb.typenamewhere #tb.months='总任务额度'--加上任务调整update #tb set totalfee=totalfee+cfrom #tb inner join (select a.typename, sum(totalfee) c from #tb awhere a.months='任务调整' group by a.typename) con c.typename=#tb.typenamewhere #tb.months='总任务额度'declare @sql as varchar(4000);set @sql='select typename [活动类型]'select @sql=@sql+',isnull(max(case when months='''+months+''' then totalfee end),0) ['+months+']' from (select distinct months from #tb ) bset @sql=@sql+' from #tb t group by typename order by reverse(typename) desc';print @sqlexec(@sql)go/****** 对象: StoredProcedure [dbo].[hjtjOrigin1] 脚本日期: 07/09/2008 13:54:09 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate proc [dbo].[hjtjold]( @orgid varchar(32), @begindate varchar(12), @enddate varchar(12))as set nocount on; --办事处临时表 create table #OrgOffice( id char(32), company char(32) ) --主计划临时表 create table #mainplan( id char(32) ) --类型临时表 create table #type( id char(32) --pid varchar(32), --typeName varchar(32) --上级类型的名称，如(非终端,周边开发) ); create table #tb( typename varchar(30), totalfee numeric(18,3), months varchar(32), totalamount numeric(20,3) ) declare @regionS as varchar(500); --片区 set @regions='402881ba0d6777c5010d68a41ee70037,402881ba0d6777c5010d68a7432f003b,402881ba0d6777c5010d68ab52c30047,402881ba0d6777c5010d68abcc94004a,402881ba0d6777c5010d68ac318b004d' --取出该类型的全部子类型(包括自己) /* insert into #type(id,pid,typeName) select id,pid,'终端类型' from selectitem where pid='402881e80caa9192010caa971be5000c' union all union all select id,pid,'非终端类型' from selectitem a where a.pid='402881e80caa9192010caa97560f000e' union all select id,pid,'地面广告宣传 ' from selectitem a where a.pid='402881e80caa9192010caa9806450010' union all select id,pid,'周边开发 ' from selectitem a where a.pid='402881e80caa9192010caa9989c80015' union all select id,id,'总部宣传品 ' from selectitem a where a.id='402881e80caa9192010caa9868b80012' */insert into #type(id) select id from selectitem a where a.pid='402881e80caa9192010caa971be5000c' or a.typeid='402881e80caa9192010caa96a85a000a' or a.pid='402881e80caa9192010caa97560f000e' or a.pid='402881e80caa9192010caa9806450010' or a.pid='402881e80caa9192010caa9989c80015' or a.id='402881e80caa9192010caa9868b80012' --递归查询全部下属机构; with org(orgid) as ( select a.oid from orgunitlink a where a.pid=@orgid union all select c.oid from orgunitlink c inner join org aa on aa.orgid=c.pid ) insert into #OrgOffice(id) select a.orgid from org a --插入机构下的主计划 insert into #mainplan select a.id from Mfm_Mainplaninfo a inner join #orgoffice b on b.id=a.orgunitiddeclare @beginmonth as datetime;declare @endmonth as datetime;declare @beginmonth1 as varchar(10)declare @endmonth1 as varchar(10)set @beginmonth=cast(@begindate as datetime);set @endmonth=cast(@enddate as datetime);while @beginmonth<=@endmonthbegin set @beginmonth1=convert(varchar(10),@beginmonth,120) set @endmonth1=convert(varchar(10),@endmonth,120) if(dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime))>=@enddate)--结束时间在小于当月最后一天 begin --终端促销日计划金额,日任务量 insert into #tb(typename,totalfee,months,totalamount) select '终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) from Mfm_terminalplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=0 union all --非终端日计划金额,日任务量select '非终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) from Mfm_Noterminalplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=0 union all--地面广告宣传日计划金额,日任务量select '地面宣传活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) from Mfm_Groundplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=0 union all--周边开发select '周边开发活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) from Mfm_Areadevplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=0 union all--总部宣传品 select '总部宣传品活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) from Mfm_Groupmaterialplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=0 --媒体类型union allselect at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3))from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mmon mm.id = a1.mediaplanid cross join selectitem at where a1.mediaplanid in(select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo mdon m.id = md.mediaplanid inner join selectitem a on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeidinner join #mainplan con m.mainplanid =c.idwhere md.begindate <= @endmonth1 andmd.enddate >=@beginmonth1and md.begindate<=md.enddate and md.mark=0)and a1.mark=0and at.id=mm.mediatypeidand at.typeid='402881e80caa9192010caa96a85a000a'group by at.objname--111 select * from #tb--插入没有该媒体类型（电视，网络等)数据为0insert into #tb(typename,totalfee,months)select a.objname,0,convert(varchar(7),@beginmonth,120) from selectitem a where a.typeid='402881e80caa9192010caa96a85a000a' andnot exists (select 1 from #tb t where t.typename=a.objname and t.months=convert(varchar(7),@beginmonth,120))set @beginmonth=dateadd(month,3,@endmonth);--设置时间大于结束时间，循环结束endelse --结束时间在大于当月最后一天begin set @endmonth=dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime));--本月的最后一天 if(@endmonth>=@enddate) set @endmonth=@enddate;--假如下一个月最后一天大于结束时间 set @beginmonth1=convert(varchar(10),@beginmonth,120) set @endmonth1=convert(varchar(10),@endmonth,120)--运行计算 insert into #tb(typename,totalfee,months,totalamount) select '终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) from Mfm_terminalplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=0 union all --非终端日计划金额,日任务量select '非终端促销活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) from Mfm_Noterminalplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=0 union all--地面广告宣传日计划金额,日任务量select '地面宣传活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) from Mfm_Groundplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=0 union all--周边开发select '周边开发活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) from Mfm_Areadevplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=0 union all--总部宣传品 select '总部宣传品活动', cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) from Mfm_Groupmaterialplan inner join Mfm_Mainplaninfo a on mainplanid=a.id inner join #orgoffice b on b.id=a.orgunitid inner join #type on acttype=#type.id inner join orgunit on orgunit.id=b.id inner join selectitem k on k.id=#type.id where begindate<=@endmonth1 and enddate>=@beginmonth1 and begindate<=enddate and mark=0 --媒体类型union allselect at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) 总投入 ,convert(varchar(7),@beginmonth,120) ,cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate)) as numeric(22,3)) from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mmon mm.id = a1.mediaplanid cross join selectitem at where a1.mediaplanid in(select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo mdon m.id = md.mediaplanid inner join selectitem a on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeidinner join #mainplan con m.mainplanid =c.idwhere md.begindate <= @endmonth1 andmd.enddate >=@beginmonth1and md.begindate<=md.enddate and md.mark=0)and a1.mark=0and at.id=mm.mediatypeidand at.typeid='402881e80caa9192010caa96a85a000a'group by at.objname--select * from #tbinsert into #tb(typename,totalfee,months)select a.objname,0,convert(varchar(7),@beginmonth,120) from selectitem a where a.typeid='402881e80caa9192010caa96a85a000a' andnot exists (select 1 from #tb t where t.typename=a.objname and t.months=convert(varchar(7),@beginmonth,120))--计算完毕后,开始时间为下个月的第一天 set @beginmonth=cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime); set @endmonth=@enddate --结束时间为默认结束时间endend--计算总投入insert into #tb(typename,totalfee,months)select typename,sum(totalfee),'总投入'from #tb group by typename--任务调整insert into #tb(typename,totalfee,months)select a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406 inner join selectitem a on a.id=field005where field011='4028827015f58e7901161d0b63236364'and field012='40288141103870fb0110487902250403'and field005in ('402881e80caa9192010caa971be5000c' --终端类型, '402881e80caa9192010caa97560f000e' --非终端类型,'402881e80caa9192010caa9806450010' --地面广告宣传,'402881e80caa9192010caa9989c80015' --周边开发,'402881e80caa9192010caa9868b80012'--总部宣传品)and field018='1' and field002 in (select id from #orgoffice)and field027 is null --为null为原始计划group by a.objnameunion allselect a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406 inner join selectitem a on a.typeid='402881e80caa9192010caa96a85a000a'and a.id=field025where field011='4028827015f58e7901161d0b63236364'and field012='40288141103870fb0110487902250402'and field018='1' and field002 in (select id from #orgoffice )and field027 is null --为null为原始计划group by a.objname--通过媒体类型聚合--预留额度（机动费）insert into #tb(typename,totalfee,months)select ks.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget ainner join #mainplan b on b.id=a.mainplanidinner join (select top 1 * from Mfm_Assessinfo where dbo.getdays(@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.idinner join selectitem k on k.id=a.acttypeidand (k.pid='402881e80caa9192010caa97560f000e' or k.pid='402881e80caa9192010caa971be5000c' or k.pid='402881e80caa9192010caa9806450010' or k.pid='402881e80caa9192010caa9989c80015')inner join selectitem ks on ks.id=k.pidand isactive='0'group by ks.objnameunion all --电视媒体,总部宣传品select k.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget ainner join #mainplan b on b.id=a.mainplanid inner join (select top 1 * from Mfm_Assessinfo where dbo.getdays(@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.idinner join selectitem k on k.id=a.acttypeid and (k.typeid='402881e80caa9192010caa96a85a000a'or k.id='402881e80caa9192010caa9868b80012')and isactive='0'group by k.objname--插入没有数据的机动费，默认设置为0insert into #tb(typename,totalfee,months)select a.objname,0,'预留机动费' from selectitem a where a.id='402881e80caa9192010caa971be5000c' or a.typeid='402881e80caa9192010caa96a85a000a' or a.id='402881e80caa9192010caa97560f000e' or a.id='402881e80caa9192010caa9806450010' or a.id='402881e80caa9192010caa9989c80015' or a.id='402881e80caa9192010caa9868b80012' and not exists(select * from #tb c where c.months='预留机动费' and a.objname=c.typename)--计算总任务额度(预留机动费+任务调整+总投入)--插入总投入insert into #tb(typename,totalfee,months)select typename,sum(totalamount),'总任务额度'from #tb where totalamount>0 group by typename--加上预留机动费update #tb set totalfee=totalfee+cfrom #tb inner join (select a.typename, sum(totalfee) c from #tb awhere a.months='预留机动费' group by a.typename) con c.typename=#tb.typenamewhere #tb.months='总任务额度'--加上任务调整update #tb set totalfee=totalfee+cfrom #tb inner join (select a.typename, sum(totalfee) c from #tb awhere a.months='任务调整' group by a.typename) con c.typename=#tb.typenamewhere #tb.months='总任务额度'--输出数据declare @sql as varchar(4000);set @sql='select typename [活动类型]'select @sql=@sql+',isnull(max(case when months='''+months+''' then totalfee end),0) ['+months+']' from (select distinct months from #tb ) bset @sql=@sql+' from #tb t group by typename order by reverse(typename) desc';print @sqlexec(@sql)go