问题：现知道总量qty,和开始时间starttime 及 结束时间 endtime ,若要实现以下结果怎么写sql呢？
starttime endtime qty
---------------------------------------
2008/8/12 2010/3/6 790
想要实现的结果：
starttime1 endtime1 year days=endtime1 - starttime1 qty1=days*qty/(endtime-starttime)
----------------------------------------------------
2008/8/12 2008/12/31 2008
2009/1/1 2009/12/31 2009
2010/1/1 2010/3/6 2010
------解决方案--------------------
- SQL code
set nocount ondeclare @a table(starttime datetime,endtime datetime,qty int) insert into @a select '2008/08/12','2010/03/06',790 select t.*,year(t.starttime1) [year], days=datediff(day,t.starttime1,t.endtime1), qty1=ceiling(datediff(day,t.starttime1,t.endtime1)*(select cast(qty as dec(18,4))/datediff(day,starttime,endtime) from @a))from ( select starttime1=case when year(dateadd(yy,b.id-1,a.starttime)) = year(a.starttime) then a.starttime else ltrim(year(dateadd(yy,b.id-1,a.starttime)))+'/01/01' end, endtime1=case when year(a.endtime) > year(dateadd(yy,b.id-1,a.starttime)) then ltrim(year(dateadd(yy,b.id-1,a.starttime)))+'/12/31' else a.endtime end from @a a, (select id from sysobjects where id<=(select datediff(yy,starttime,endtime)+1 from @a) ) as b ) t-->结果(有点尾差)starttime1 endtime1 year days qty1 ----------------------- ---------------------- ---- ---- ----2008-08-12 00:00:00.000 2008-12-31 00:00:00.000 2008 141 1962009-01-01 00:00:00.000 2009-12-31 00:00:00.000 2009 364 5042010-01-01 00:00:00.000 2010-03-06 00:00:00.000 2010 64 89