根据期间日期生成行集(by libin_ftsafe)

 

 

现在有一个表为：
起始日期        终止日期        期间任务额        任务人
2009-01-01    2009-01-5    100            a1
2009-01-01    2009-01-2    240            a2

如何根据一条SQL查询，获得如下的表:

任务日期        本日任务额        任务人
2009-01-01      20            a1
2009-01-02      20            a1
2009-01-03      20            a1
2009-01-04      20            a1
2009-01-05      20            a1
2009-01-01      120            a2
2009-01-02      120            a2

其中，本日任务额为期间任务额的日均.
望高手赐教！

--生成测试数据
declare @tname table(起始日期 datetime,终止日期 datetime,期间任务额 int,任务人 varchar(10))
insert into @tname select '2009-01-01','2009-01-5',100,'a1'
insert into @tname select '2009-01-01','2009-01-2',240,'a2'

--借助系统表生成辅助列以实现功能
select
    dateadd(dd,a.num,b.起始日期) as 任务日期,
    期间任务额/(datediff(dd,b.起始日期,b.终止日期)+1) as 本日任务额,
    b.任务人
from
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a,
    @tname b
where
    datediff(dd,b.起始日期,b.终止日期)>=a.num
order by
    任务人,任务日期

--输出结果
/*
任务日期                                                本日任务额       任务人       
------------------------------------------------------ ----------- ----------
2009-01-01 00:00:00.000                                20          a1
2009-01-02 00:00:00.000                                20          a1
2009-01-03 00:00:00.000                                20          a1
2009-01-04 00:00:00.000                                20          a1
2009-01-05 00:00:00.000                                20          a1
2009-01-01 00:00:00.000                                120         a2
2009-01-02 00:00:00.000                                120         a2
*/

Frankly，在楼主的表中，如果起始日期与终止日期之间的日期跨度太大，可能超出通过系统表生成的序列范围。
如果这样的假定存在，可以借助临时表/表变量等手段生成一个大的时间序列，然后再关联查询：
--生成测试数据
declare @tname table(起始日期 datetime,终止日期 datetime,期间任务额 int,任务人 varchar(10))
insert into @tname select '2009-01-01','2009-01-5',100,'a1'
insert into @tname select '2009-01-01','2009-01-2',240,'a2'

--借助表变量生成一个从0起始的1000连续数据的序列
declare @tnum table(num int identity(0,1),id int)
insert into @tnum(id) select top 1000 a.id from sysobjects a,syscolumns b

--借助系统表生成辅助列以实现功能
select
    dateadd(dd,a.num,b.起始日期) as 任务日期,
    期间任务额/(datediff(dd,b.起始日期,b.终止日期)+1) as 本日任务额,
    b.任务人
from
    @tnum a,@tname b
where
    datediff(dd,b.起始日期,b.终止日期)>=a.num
order by
    任务人,任务日期


--输出结果
/*
任务日期                                                   本日任务额       任务人       
------------------------------------------------------ ----------- ----------
2009-01-01 00:00:00.000                                20          a1
2009-01-02 00:00:00.000                                20          a1
2009-01-03 00:00:00.000                                20          a1
2009-01-04 00:00:00.000                                20          a1
2009-01-05 00:00:00.000                                20          a1
2009-01-01 00:00:00.000                                120         a2
2009-01-02 00:00:00.000                                120         a2
*/