--申请表

[申请表]的字段：aid自动增，姓名，数值，年，时间段

aid    姓名    数值    年    时间段

1    张三    100    2012    1.1-1.31
2    李四    150    2012    1.1-1.31
3    王五    130    2012    1.1-1.31
4    张三    120    2012    2.1-2.29
5    李四    110    2012    2.1-2.29
6    王五    120    2012    2.1-2.29


--审批表
[审批表]的字段：bid自动增，姓名，数值，年，时间段

bid    姓名    数值    年    时间段

1    张三    90    2012    1.1-1.31
2    李四    120    2012    1.1-1.31
3    王五    100    2012    1.1-1.31
4    张三    100    2012    2.1-2.29




--[汇总表]的字段：cid自动增，姓名，核定值，备注。



汇总数据，要求计算核定值，计算备注里的差值。


---计算

核定值计算方式：
如果上月审批数值不为空，则：
   核定值=上月申请数值+（前月审批数值-前月申请数值）+（上月审批数值-上月申请数值）
如果上月审批数值为空，则：
   核定值=上月申请数值+（前月审批数值-前月申请数值）
差值计算方式：
   前月差值=前月审批数值-前月申请数值
   上月月差值=上月审批数值-上月申请数值


注：假定当前为3月，上月为2月，前月为1月。



----示例

现在举例说明 核定值的计算方式。
假设当前时间为2012.3.1,汇总2月所有的[申请表]和[审批表]的数据到[汇总表]。
假设变量a,b,c,d，汇总张三的数据，

a=[申请表]中 张三 在 时间段1.1-1.31 的数值(100)
b=[审批表]中 张三 在 时间段1.1-1.31 的数值(90)
c=[申请表]中 张三 在 时间段2.1-2.29  的数值(120)
d=[审批表]中 张三 在 时间段2.1-2.29  的数值(100)

1月份张三的差值为：1月差值=b-a
2月份张三的差值为：2月差值=d-c

2月份张三的核定值为：核定值=c+(b-a)+(d-c)

其他人的汇总也是这样算。

因审批表2012.2.1-2.29李四王五没有审批，所以2月没有差值。

汇总表
cid    姓名    核定值    备注
1    张三    90    2012.1.1-1.31申请100，差值-10；2012.2.1-2.29申请120，差值-20；
2    李四    80    2012.1.1-1.31申请150，差值-30；2012.2.1-2.29申请110；
3    王五    90    2012.1.1-1.31申请130，差值-30；2012.2.1-2.29申请120；



-----问题

问：使用存储过程，如何汇总[申请表]和[审批表]的数据到[汇总表]？

if object_id('申请表') is not null drop table 申请表
create table 申请表 
(aid int identity(1,1),姓名 varchar(4),数值 int,年 int,时间段 varchar(8),月份 int)
insert into 申请表
select '张三',100,2012,'1.1-1.31',1 union all
select '李四',150,2012,'1.1-1.31',1 union all
select '王五',130,2012,'1.1-1.31',1 union all
select '张三',120,2012,'2.1-2.29',2 union all
select '李四',110,2012,'2.1-2.29',2 union all
select '王五',120,2012,'2.1-2.29',2

if object_id('审批表') is not null drop table 审批表
create table 审批表 (bid int,姓名 varchar(4),数值 int,年 int,时间段 varchar(8),月份 int)
insert into 审批表
select 1,'张三',90,2012,'1.1-1.31',1 union all
select 2,'李四',120,2012,'1.1-1.31',1 union all
select 3,'王五',100,2012,'1.1-1.31',1 union all
select 4,'张三',100,2012,'2.1-2.29',2

--假设当前月是2012-03月
declare @date datetime set @date='2012-03-01'
;with maco as
( 
select a.*,b.数值 as 数值1 from 申请表 a left join
审批表 b on a.姓名=b.姓名 and a.年=b.年
and a.时间段=b.时间段 where 
datediff(m,cast(ltrim(a.年)+'-'+ltrim(a.月份)+'-01' as datetime),@date)<3
)
, t as
(
select *,
datediff(m,cast(ltrim(年)+'-'+ltrim(月份)+'-01' as datetime),@date) as 月类型
from maco
)

select 姓名,
核定值=(case when (select 数值1 from t where 月类型=1 and 姓名=m.姓名) is not null
then (select sum(数值1) from t where 姓名=m.姓名)-(select sum(数值) from t where 姓名=m.姓名
and 月类型=2) else 
(select sum(isnull(数值,0)) from t where 姓名=m.姓名
and 月类型=1)+(select sum(isnull(数值1,0)) from t where 姓名=m.姓名
and 月类型=2)-(select sum(isnull(数值,0)) from t where 姓名=m.姓名
and 月类型=2)
end),
备注=(select ltrim(年)+'.'+时间段+'申请'+ltrim(数值) from t where 月类型=2 and 姓名=m.姓名)+
'，差值'+ltrim((select 数值1 from t where 姓名=m.姓名
and 月类型=2)-(select 数值 from t where 姓名=m.姓名 and 月类型=2))+'；'+
(select ltrim(年)+'.'+时间段+'申请'+ltrim(数值) from t where 月类型=1 and 姓名=m.姓名)
+isnull('，差值'+ltrim((select 数值1 from t where 姓名=m.姓名
and 月类型=1)-(select 数值 from t where 姓名=m.姓名 and 月类型=1)),'')+'；'
from t m group by 姓名 
order by charindex(姓名,'张三,李四,王五')

--order by 这个的排序就是为了结果和你的结果排序一致。
/*
姓名   核定值         备注
---- ----------- ------------------------------------------
张三   90          2012.1.1-1.31申请100，差值-10；2012.2.1-2.29申请120，差值-20；
李四   80          2012.1.1-1.31申请150，差值-30；2012.2.1-2.29申请110；
王五   90          2012.1.1-1.31申请130，差值-30；2012.2.1-2.29申请120；
*/