求关于时间段的sql语句算法

[tteagle]

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

002 20070810 20070815 500.00
002 20070810 20070820 600.00
002 20070810 20070821 0.00

003 20070910 20070930 1000.00
003 20070910 29999999 1000.00

要求计算每个帐号在2007/8月的每天平均余额
例如001的余额就是（1000*5+1200*26）/31
002就是（8*10+5*600+11*0)/31
003就是0


关键就是相当于求每个时间段分别在某个已定义的固定时间段内的天数
例如有下面n个时间段
1，StartDate---date1
2，date1 ---date2
3，date2 ---date3
4，date3 ---date4
.....
n,date4 ---dateX
e,dateX ---EndDate

求上面每个时间段在某个固定时间段（例如t1---t2）的天数
昨天有位DX帮我写了一个，但是时间段方面还是有点漏洞，所以今天再求了:)
我自己写的晕头转向，求比较好的思路

[dawugui]

例如001的余额就是（1000*5+1200*26）/31
002就是（8*10+5*600+11*0)/31
003就是0

你这是咋计算的?没看出.

[tteagle]

因为001帐户在20070806变动之前应该按照上个时间段余额1000来计算，从20070801-20070805一共5天
然后在20070806一直到20070831都没有变化了，所以应该按照26天来计算
这样就是（1000*5+1200*26）了

002是我写错了，应该是
(5*500+5*600+11*0)/31

[dawugui]

因为001帐户在20070806变动之前应该按照上个时间段余额1000来计算，从20070801-20070805一共5天
然后在20070806一直到20070831都没有变化了，所以应该按照26天来计算
这样就是（1000*5+1200*26）了


就你说的这个,没看到相应的数据.

[Limpire]

http://topic.csdn.net/u/20071007/17/0c0cf6c8-1663-4c84-8b68-fc82bcac04e9.html

tteagle 发表于：2007-10-07 20:10:09
frrrrrrrrrrrrrrrrrrrrrrrrrr日日日日


昨天是我写的。我得罪你啦，需要用这个字？

我没叫你不测试就结贴，也没要求你给我分，对吧？

你昨天没提醒1个月内可能变动多次，对吧？
好，这个问题算我考虑不周，抱歉！

002是我写错了，应该是
(5*500+5*600+11*0)/31

这个仍然错，应该是(15*500+5*600+11*0)/31。
不管按哪个式子，计算规则和001都不同，懂么？

如果要计算200702的平均，还需要初始余额，懂么？

自己都理不清应该怎么计算，好意思“日”了？

[tteagle]

不会吧，我的题目还是没写明白？

以001为例，一共四个时间段，是连续的
001 20070205 20070506 1000.00
001 20070205 20070806 1200.00
001 20070205 20071106 1400.00
001 20070205 29999999 1400.00

时间段的变化就是20070205--20070506--20070806--20071106------29999999
现在就是求上面那几个时间段在（20070801-20070831）里面的天数

001 20070205 20070506 1000.00 ----0天
001 20070506 20070806 1200.00 ----5天
001 20070806 20071106 1400.00 ----26天
001 20071106 29999999 1400.00 ----0天

不知道这样是否把问题讲清楚了？

[晓风残月0110]

select 有点乱 from lz

[tteagle]

to Limpire
关于
“tteagle 发表于：2007-10-07 20:10:09
frrrrrrrrrrrrrrrrrrrrrrrrrr日日日日 ”

晕，昨天是碰到键盘了，csdn就直接提交了，你帮我解决了问题，我感激还来不及呢，怎么可能会有不敬的想法？？

再次感激你帮我提供了很好的思路，谢谢。

[Limpire]

还不明白？把002也按上面的列出来看看！死蠢

[Limpire]

不用谢我，问题还没解决！我对上面的回复道歉！

[dawugui]

001 20070205 20070506 1000.00 ----0天
001 20070506 20070806 1200.00 ----5天 这个5怎么算出来的?
001 20070806 20071106 1400.00 ----26天
001 20071106 29999999 1400.00 ----0天

[tteagle]

002我还是写错了........

002 20070810 20070815 500.00 5天
002 20070810 20070820 600.00 10天
002 20070810 20070821 0.00


(5*500+10*600+11*0)/31

不知道这次错了没有......

[tteagle]

to limpire,
真的非常感谢你，真的请你不要误会了....

to dawugui
001 20070205 20070506 1000.00 ----0天
001 20070506 20070806 1200.00 ----5天 这个5怎么算出来的? 这个就是从20070801到20070805，一共5天
001 20070806 20071106 1400.00 ----26天
001 20071106 29999999 1400.00 ----0天

唉，我真是笨，程序不会写，连问题都不会提,害得DX们看不明白。。。。。

[Limpire]

002 20070810 20070815 500.00
002 20070810 20070820 600.00
002 20070810 20070821 0.00
002 20070810 20070825 100.00

再变更一次，怎么算，是不是把0的纪录拿掉就行了

[Limpire]

我误会就不会继续跟贴。

[dawugui]

终于看明白了.==,我测试一下.

[dawugui]

create table tb(帐号 varchar(10),起算日期 datetime,变动日期 datetime,余额 int)
insert into tb values('001', '20070205', '20070506', 1000.00)
insert into tb values('001', '20070205', '20070806', 1200.00)
insert into tb values('001', '20070205', '20071106', 1400.00)
insert into tb values('001', '20070205', null, 1400.00)
insert into tb values('002', '20070810', '20070815', 500.00)
insert into tb values('002', '20070810', '20070820', 600.00)
insert into tb values('002', '20070810', '20070821', 0.00)
insert into tb values('003', '20070910', '20070930', 1000.00 )
insert into tb values('003', '20070910', null, 1000.00 )
go
select top 1000 id=identity( int,0,1) into tmp from syscolumns a,syscolumns b

select 帐号 , cast(sum(cast(余额 as decimal(18,2)))/31 as decimal(18,2)) 平均余额 from
(
select 帐号,dateadd(day,tmp.id,起算日期) 日期,余额
from tb,tmp
where dateadd(day,tmp.id,起算日期) <= 变动日期
) t
where month(日期) = 8
group by 帐号

drop table tb,tmp

/*
帐号 平均余额
---------- --------------------
001 1632.26
002 309.68

（所影响的行数为 2 行）
*/

[dawugui]

create table tb(帐号 varchar(10),起算日期 datetime,变动日期 datetime,余额 int)
insert into tb values('001', '20070205', '20070506', 1000.00)
insert into tb values('001', '20070205', '20070806', 1200.00)
insert into tb values('001', '20070205', '20071106', 1400.00)
insert into tb values('001', '20070205', null, 1400.00)
insert into tb values('002', '20070810', '20070815', 500.00)
insert into tb values('002', '20070810', '20070820', 600.00)
insert into tb values('002', '20070810', '20070821', 0.00)
insert into tb values('003', '20070910', '20070930', 1000.00 )
insert into tb values('003', '20070910', null, 1000.00 )
go
select top 1000 id=identity(int,0,1) into tmp from syscolumns a,syscolumns b

select t1.帐号 , isnull(t2.平均余额,0) 平均余额 from
(select distinct 帐号 from tb) t1
left join
(
select 帐号 , cast(sum(cast(余额 as decimal(18,2)))/31 as decimal(18,2)) 平均余额 from
(
select 帐号,dateadd(day,tmp.id,起算日期) 日期,余额
from tb,tmp
where dateadd(day,tmp.id,起算日期) <= 变动日期 and 变动日期 is not null
) t
where month(日期) = 8
group by 帐号
) t2
on t1.帐号 = t2.帐号

drop table tb,tmp

/*
帐号 平均余额
---------- --------------------
001 1632.26
002 309.68
003 .00

（所影响的行数为 3 行）
*/

[Limpire]

--原始数据：@T
declare @T table(帐号 varchar(3),起算日期 varchar(8),变动日期 varchar(8),余额 money)
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','20070810',20070815,500.00 union all
select '002','20070810',20070820,600.00 union all
select '002','20070810',20070821,0.00 union all
select '003','20070910',20070930,1000.00 union all
select '003','20070910',29999999,1000.0

/*
001	(1000*5+1200*26)/31 
002	(5*500+10*600+11*0)/31
003	0
*/

declare @1 varchar(8),@2 varchar(8)
select @1='20070801',@2=convert(varchar(8),dateadd(month,1,@1),112)
--更改 @1 测试其它月份

declare @Days int,@Seed int
select @Days=datediff(day,@1,@2),@Seed=datediff(day,0,@1)
exec ('select top '+@Days+' iniDate=identity(int,'+@Seed+',1) into ##iniDate from syscolumns')

select a.帐号,变动日期=convert(varchar(8),cast(b.iniDate as datetime),112),c.余额
into #Result from
(select 帐号 from @T group by 帐号) a cross join ##iniDate b
left join
(select 帐号,变动日期=case when 变动日期<@1 then @1 else 变动日期 end,余额 from @T a where 变动日期=(select max(变动日期) from @T where 帐号=a.帐号 and 变动日期<@1) or left(变动日期,6)=left(@1,6)) c
on a.帐号=c.帐号 and convert(varchar(8),cast(b.iniDate as datetime),112)=c.变动日期
order by a.帐号,b.iniDate

update #Result set 余额=0 where day(变动日期)=1 and 余额 is null
update a set a.余额=(select top 1 余额 from #Result where 帐号=a.帐号 and 变动日期<=a.变动日期 and 余额 is not null order by 变动日期 desc) from #Result a

select 帐号,平均余额=avg(余额) from #Result group by 帐号
/*
帐号	平均余额
001	1167.7419
002	100.0000
003	.0000
*/

--删除临时表
drop table ##iniDate,#Result

[tteagle]

谢谢两位大侠，好像limpire DX的结果是准确的，dawugui DX估计就是计算时多一天少一天了，我自己再慢慢看看，谢谢两位。