用单一存储过程计算报表，替换以前用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 @beginmnotallow=cast(@begindate as datetime);
set @endmnotallow=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.mnotallow=convert(varchar(7),@beginmonth,120))
set @beginmnotallow=dateadd(month,3,@endmonth);--设置时间大于结束时间，循环结束
end
else --结束时间在大于当月最后一天
begin
--set @beginmnotallow=@begindate;
set @endmnotallow=dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime));--本月的最后一天
if(@endmonth>=@enddate)
set @endmnotallow=@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.mnotallow=convert(varchar(7),@beginmonth,120))
--计算完毕后,
set @beginmnotallow=cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime);--开始时间为下个月的第一天
set @endmnotallow=@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.mnotallow='预留机动费' 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.mnotallow='预留机动费'
group by a.typename) c
on c.typename=#tb.typename
where #tb.mnotallow='总任务额度'
--加上任务调整
update #tb set totalfee=totalfee+c
from #tb inner join
(select a.typename, sum(totalfee) c from #tb a
where a.mnotallow='任务调整'
group by a.typename) c
on c.typename=#tb.typename
where #tb.mnotallow='总任务额度'
declare @sql as varchar(4000);
set @sql='select typename [活动类型]'
select @sql=@sql+',isnull(max(case when mnotallow='''+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 @reginotallow='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 @beginmnotallow=cast(@begindate as datetime);
set @endmnotallow=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.mnotallow=convert(varchar(7),@beginmonth,120))
set @beginmnotallow=dateadd(month,3,@endmonth);--设置时间大于结束时间，循环结束
end
else --结束时间在大于当月最后一天
begin
set @endmnotallow=dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime));--本月的最后一天
if(@endmonth>=@enddate)
set @endmnotallow=@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.mnotallow=convert(varchar(7),@beginmonth,120))
--计算完毕后,开始时间为下个月的第一天
set @beginmnotallow=cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime);
set @endmnotallow=@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.mnotallow='预留机动费' 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.mnotallow='预留机动费'
group by a.typename) c
on c.typename=#tb.typename
where #tb.mnotallow='总任务额度'
--加上任务调整
update #tb set totalfee=totalfee+c
from #tb inner join
(select a.typename, sum(totalfee) c from #tb a
where a.mnotallow='任务调整'
group by a.typename) c
on c.typename=#tb.typename
where #tb.mnotallow='总任务额度'
--输出数据
declare @sql as varchar(4000);
set @sql='select typename [活动类型]'
select @sql=@sql+',isnull(max(case when mnotallow='''+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