按时间段范围查询 设定时间段内数据？

[wuyongbiao]

有如下需求，数据表TarOutput结结构为：
开始日期 结束日期 各阶段每天目标值
ID StartDate EndDate A B C D E F
1 2010/5/17 2010/5/22 9000 8000 8000 8000 8500 9000
2 2010/5/24 2010/5/29 9000 8000 8000 8000 8500 9000
3 2010/5/31 2010/6/5 8000 8000 8000 8000 8500 8000
4 2010/6/7 2010/6/19 9000 8000 8000 8000 8500 9000
......

开始日期 和 结束日期不会交叉，表TarOutput为预先设置，请问
如果我要查询任一时间段 比如 d1 = 2010/5/18 和 d2=2010/5/28 日期范围内各阶段总目标值（未设置日期，
其各阶段值默认为0 如2010/5/23日。
要统计出2010/5/18至2010/5/28各阶段每天目标值总和，这个SQL该如何实现？

[pt1314917]

结果应该是什么样的？

[wuyongbiao]

统计出2010/5/18至2010/5/28各阶段每天目标值(随意设定的)总和：即

5/18 5/19 5/20 5/21 5/22 5/23 5/24 5/25 5/26 5/27 5/28
SUM（A）= 9000 +9000 +9000 +9000 +9000 + 0 + 9000 + 9000 + 9000 +9000+ 9000 = 90000
SUM（B）= 8000 +8000+8000 +8000 +8000 + 0 + 8000 + 8000 + 8000 +8000+ 8000 = 80000
SUM（C）= 8000 +8000+8000 +8000 +8000 + 0 + 8000 + 8000 + 8000 +8000+ 8000 = 80000
SUM（D）= 8000 +8000+8000 +8000 +8000 + 0 + 8000 + 8000 + 8000 +8000+ 8000 = 80000
SUM（E）= 8500 +8500+8500 +8500 +8500 + 0 + 8500 + 8500 + 8500 +8500+ 8500 = 85000
SUM（F）= 9000 +9000 +9000 +9000 +9000 + 0 + 9000 + 9000 + 9000 +9000+ 9000 = 90000

[rmljoe]

-- 建测试数据
if object_id('tb') is not null 
drop table tb
create table tb(ID int, StartDate datetime, EndDate datetime, A int, B int, C int, D int, E int, F int)
insert into tb select 1,'2010/5/17','2010/5/22',9000,8000,8000,8000,8500,9000
     union all select 2,'2010/5/24','2010/5/29',9000,8000,8000,8000,8500,9000
     union all select 3,'2010/5/31','2010/6/5',8000,8000,8000,8000,8500,8000
     union all select 4,'2010/6/7','2010/6/19',9000,8000,8000,8000,8500,9000


-- 先取到涉及到日期段，及此日期段占的天数，
if object_id('tempdb..#t') is not null 
drop table #t

declare @fromdate datetime,@todate datetime
select @fromdate = '2010/5/18',@todate = '2010/5/28'

select 
datediff(dd,
(case when @fromdate > dateadd(dd,-1,startdate) and  @fromdate <= dateadd(dd,-1,enddate) then @fromdate else startdate end),
(case when @todate > dateadd(dd,-1,startdate) and  @todate <= dateadd(dd,-1,enddate) then @todate else enddate end)) + 1 as days,
*
into #t
from tb
where @fromdate  <= enddate  and @todate > dateadd(dd,-1,startdate) 

/*
-- select * from #t
days        ID          StartDate               EndDate                 A           B           C           D           E           F
----------- ----------- ----------------------- ----------------------- ----------- ----------- ----------- ----------- ----------- -----------
5           1           2010-05-17 00:00:00.000 2010-05-22 00:00:00.000 9000        8000        8000        8000        8500        9000
5           2           2010-05-24 00:00:00.000 2010-05-29 00:00:00.000 9000        8000        8000        8000        8500        9000

(2 行受影响)
*/


-- 加总
select
sum(A1) as totalA,
sum(B1) as totalB,
sum(C1) as totalC,
sum(D1) as totalD,
sum(E1) as totalE,
sum(F1) as totalF
from 
(select
sum(b.A)* a.days as A1,
sum(b.B)* a.days as B1,
sum(b.C)* a.days as C1,
sum(b.D)* a.days as D1,
sum(b.E)* a.days as E1,
sum(b.F)* a.days as F1
from #t a
left join tb b
on a.id = b.id
group by a.id,b.id,a.days) m

/*
totalA      totalB      totalC      totalD      totalE      totalF
----------- ----------- ----------- ----------- ----------- -----------
90000       80000       80000       80000       85000       90000

(1 行受影响)

*/