求一条sql语句

[harryCom]

已知这个table:
StartDate EndDate Cost
2005-01-23 2005-01-29 10
2005-01-30 2005-02-05 14
2005-02-06 2005-02-12 20

想得到的结果：
StartDate EndDate Cost
2005-01-23 2005-01-29 10
2005-01-30 2005-01-31 4
2005-02-01 2005-02-05 10
2005-02-06 2005-02-12 20

[sdhdy]

用游标＋临时表实现

[xingfuniao]

感觉无规律可循....让高手来吧

[txlicenhe]

看不懂。

[Softlee81307]

Create Table tk(StartDate DateTime,EndDate DateTime,Cost int)
insert Into tk
select '2005-01-23','2005-01-29',10 union all
select '2005-01-30','2005-02-05',14 union all
select '2005-02-06','2005-02-12',20
-----------------------------------------------------------------------------
-----------------以下是輸出語句---------------------------------------

select * from tk where datediff(m,startdate,enddate)=0
union all
select startDate,dateadd(dd,-1,convert(varchar(7),EndDate,120)+'-01'),
(datediff(dd,startdate,dateadd(dd,-1,convert(varchar(7),EndDate,120)+'-01'))+1)*
(cost/datediff(dd,startDate,EndDate)) from tk where datediff(m,startdate,enddate)>0
union all
select convert(varchar(7),EndDate,120)+'-01',enddate,
(datediff(dd,convert(varchar(7),EndDate,120)+'-01',EndDate)+1)*
(cost/datediff(dd,startDate,EndDate))
from tk where datediff(m,startdate,enddate)>0
---------------------結束------------------------------------
--------------結果-----------------------------------

StartDate EndDate Cost
2005-01-23 00:00:00.000 2005-01-29 00:00:00.000 10
2005-02-06 00:00:00.000 2005-02-12 00:00:00.000 20
2005-01-30 00:00:00.000 2005-01-31 00:00:00.000 4
2005-02-01 00:00:00.000 2005-02-05 00:00:00.000 10

Drop Table Tk ---刪除測試

[Softlee81307]

Create Table tk(StartDate DateTime,EndDate DateTime,Cost int)
insert Into tk
select '2005-01-23','2005-01-29',10 union all
select '2005-01-30','2005-02-05',14 union all
select '2005-02-06','2005-02-12',20
-----------------------------------------------------------------------------
-----------------以下是輸出語句---------------------------------------
select * from (
select * from tk where datediff(m,startdate,enddate)=0
union all
select startDate,dateadd(dd,-1,convert(varchar(7),EndDate,120)+'-01'),
(datediff(dd,startdate,dateadd(dd,-1,convert(varchar(7),EndDate,120)+'-01'))+1)*
(cost/datediff(dd,startDate,EndDate)) from tk where datediff(m,startdate,enddate)>0
union all
select convert(varchar(7),EndDate,120)+'-01',enddate,
(datediff(dd,convert(varchar(7),EndDate,120)+'-01',EndDate)+1)*
(cost/datediff(dd,startDate,EndDate))
from tk where datediff(m,startdate,enddate)>0 ) a order by startDate
---------------------結束------------------------------------
--------------結果-----------------------------------

StartDate EndDate Cost
2005-01-23 00:00:00.000 2005-01-29 00:00:00.000 10
2005-02-06 00:00:00.000 2005-02-12 00:00:00.000 20
2005-01-30 00:00:00.000 2005-01-31 00:00:00.000 4
2005-02-01 00:00:00.000 2005-02-05 00:00:00.000 10

Drop Table Tk ---刪除測試

[Softlee81307]

Create Table tk(StartDate DateTime,EndDate DateTime,Cost int)
insert Into tk
select '2005-01-23','2005-01-29',10 union all
select '2005-01-30','2005-02-05',14 union all
select '2005-02-06','2005-02-12',20
-----------------------------------------------------------------------------
-----------------以下是輸出語句---------------------------------------
select * from (
select * from tk where datediff(m,startdate,enddate)=0
union all
select startDate,dateadd(dd,-1,convert(varchar(7),EndDate,120)+'-01'),
(datediff(dd,startdate,dateadd(dd,-1,convert(varchar(7),EndDate,120)+'-01'))+1)*
(cost/datediff(dd,startDate,EndDate)) from tk where datediff(m,startdate,enddate)>0
union all
select convert(varchar(7),EndDate,120)+'-01',enddate,
(datediff(dd,convert(varchar(7),EndDate,120)+'-01',EndDate)+1)*
(cost/datediff(dd,startDate,EndDate))
from tk where datediff(m,startdate,enddate)>0 ) a order by startDate
---------------------結束------------------------------------
--------------結果-----------------------------------

StartDate EndDate Cost
2005-01-23 00:00:00.000 2005-01-29 00:00:00.000 10
2005-01-30 00:00:00.000 2005-01-31 00:00:00.000 4
2005-02-01 00:00:00.000 2005-02-05 00:00:00.000 10
2005-02-06 00:00:00.000 2005-02-12 00:00:00.000 20


Drop Table Tk ---刪除測試

[yigebendan]

mark

[pbsql]

create table #t(StartDate datetime,EndDate datetime,Cost int)
insert #t
select '2005-01-23', '2005-01-29', 10 union all
select '2005-01-30', '2005-02-05', 14 union all
select '2005-02-06', '2005-02-12', 20

select StartDate,EndDate,Cost
from #t where datediff(month,StartDate,EndDate)=0
union all
select StartDate,
dateadd(month,1,StartDate)-day(dateadd(month,1,StartDate)),
cost*(datediff(day,StartDate,dateadd(day,-day(EndDate),EndDate))+1)/7
from #t where datediff(month,StartDate,EndDate)<>0
union all
select dateadd(month,1,StartDate)-day(dateadd(month,1,StartDate))+1,
EndDate,
cost*day(EndDate)/7
from #t where datediff(month,StartDate,EndDate)<>0
order by StartDate
drop table #t

[harryCom]

非常感谢Softlee81307(孔腎) ，问题已解决