求Sql语句，兄弟们进来帮帮忙

tteagle 2007-10-07 05:29:19

有一个表结构为
帐号起算日期变动日期余额
001 20070205 20070506 1000.00
001 20070205 20070806 1200.00
001 20070205 20071106 1400.00
001 20070205 29999999 1400.00

002 20070109 20070710 500.00
002 20070109 29999999 500.00

要求计算每个帐号在2007/8月的每天平均余额
例如001的余额就是（1000*5+1200*26）/31
002就是500*31/31=500.00

...全文

113 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

tteagle 2007-10-08

打赏
举报

limrpitr有的算法不对，例如起算日期如果大于@1=‘20070801’时就不对了

tteagle 2007-10-07

打赏
举报

frrrrrrrrrrrrrrrrrrrrrrrrrr日日日日

tteagle 2007-10-07

打赏
举报

无言感激
谢了，先给分，再慢慢学习

Limpire 2007-10-07

打赏
举报

--原始数据：@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

*/