--测试数据 create table rt(cname varchar(10) ,ddate1 datetime, ddate2 datetime) insert rt select 'a', '2005-05-01', '2005-05-30' union all select 'b', '2005-06-01', '2005-07-30' union all select 'c', '2005-04-01', '2005-07-30' --主要部分 declare @date datetime, @sdate datetime, @edate datetime select @sdate=min(case when ddate1>ddate2 then ddate2 else ddate1 end) ,@edate=max(case when ddate1>ddate2 then ddate1 else ddate2 end) from rt --得到分段表 declare @table table(ddate datetime) set @date=dateadd(mm, datediff(mm, 0,@sdate), 0) while datediff(mm, @date, @edate)>=0 begin insert @table select @date set @date=dateadd(mm, 1, @date) end --查询 select b_date=convert(varchar(7), t1.ddate, 20)+'~' +convert(varchar(7), t2.ddate, 20) ,icount=(select count(1) from rt where t2.ddate>=ddate1 and t1.ddate<=ddate2) from @table as t1 join @table as t2 on datediff(mm, t1.ddate, t2.ddate)=1 --清除 drop table rt
to 楼主:所有的记录都是同一年的吗?
icount是介于左边日期的行的数量左边的日期序列要求规定一个起始日期和终止日期,中间的自动生成...
很晕的事情
create table rt(cname varchar(10)
,ddate1 datetime, ddate2 datetime)
insert rt select 'a', '2005-05-01', '2005-05-30'
union all select 'b', '2005-06-01', '2005-07-30'
union all select 'c', '2005-04-01', '2005-07-30'
--主要部分
declare @date datetime, @sdate datetime, @edate datetime
select @sdate=min(case when ddate1>ddate2 then ddate2 else ddate1 end)
,@edate=max(case when ddate1>ddate2 then ddate1 else ddate2 end)
from rt
--得到分段表
declare @table table(ddate datetime)
set @date=dateadd(mm, datediff(mm, 0,@sdate), 0)
while datediff(mm, @date, @edate)>=0
begin
insert @table select @date
set @date=dateadd(mm, 1, @date)
end
--查询
select b_date=convert(varchar(7), t1.ddate, 20)+'~'
+convert(varchar(7), t2.ddate, 20)
,icount=(select count(1) from rt
where t2.ddate>=ddate1
and t1.ddate<=ddate2)
from @table as t1 join @table as t2
on datediff(mm, t1.ddate, t2.ddate)=1
--清除
drop table rt