日期:2014-05-18 浏览次数:20608 次
--原始数据:@T declare @T table(帐号 varchar(3),起算日期 varchar(8),变动日期 varchar(8),余额 decimal(6,2)) insert @T select '001',20070205,20070506,1000.00 union all select '001',20070205,20070806,1200.00 union all select '001',20070205,20071106,1400.00 union all select '001',20070205,29999999,1400.00 union all select '002',20070109,20070710,500.00 union all select '002',20070109,29999999,500.00 declare @1 varchar(8),@2 varchar(8) select @1 = '20070801',@2='20070901' select 帐号=coalesce(a.帐号,b.帐号), 余额=(isnull(a.余额,b.余额)*datediff(day,isnull(a.变动日期,@1),isnull(b.变动日期,@2))+ isnull(b.余额,0)*datediff(day,isnull(b.变动日期,@1),@2))/datediff(day,@1,@2) from (select 帐号,变动日期=@1,余额 from @T a where 变动日期=(select max(变动日期) from @T where 帐号=a.帐号 and 变动日期<@1)) a full join (select 帐号,变动日期,余额 from @T where left(变动日期,6)=left(@1,6)) b on a.帐号=b.帐号 /* 帐号 余额 001 1167.7419354838709 002 500.0000000000000 */