有一个表T1(startDate,EndDate) 都是datetime类型,没有降雨的是没有记录的
我想查出2003年没有降雨的日期,注意 startDate,EndDate 可能有跨度,比如startDate=2003-10-1 EndDate=2003-10-5
我想查出2003年没有降雨的日期,注意 startDate,EndDate 可能有跨度,比如startDate=2003-10-1 EndDate=2003-10-5
(
days datetime
)
declare @i int;
set @i=0;
while(@i<365)
begin
insert into #day values(dateadd(d,@i,cast('2003-01-01' as datetime)))
set @i=@i+1;
end
create table #t1
(
startDate datetime,
EndDate datetime
)
insert into #t1 values('2003-01-02','2003-01-06')
insert into #t1 values('2003-01-08','2003-01-010')Declare @sql nvarchar(4000)
set @sql='';
select @sql=@sql+' or (days>='''+cast(startDate as nvarchar)+''' and days<='''+cast(EndDate as nvarchar)+''')' from #t1
set @sql='select * from #day where days not in( select days from #day where '+substring(@sql,4,len(@sql)-3)+')';
exec(@sql);
if object_id('tempdb.dbo.#') is not null drop table #
create table #(StartTime datetime, EndTime datetime)
insert into #
select '2005-1-20 0:00:00', '2005-1-21 0:00:00' union all
select '2005-1-29 0:00:00', '2005-1-30 0:00:00' union all
select '2005-2-15 0:00:00', '2005-2-19 0:00:00' union all
select '2005-2-21 0:00:00', '2005-2-22 0:00:00'with cte as
(
select id=row_number()over(order by StartTime), * from # where 2005 between year(StartTime) and year(EndTime)
)
select isnull(a.EndTime+1,'2005')a, isnull(b.StartTime-1, '20051231')b from cte a full join cte b on a.id=b.id-1
where isnull(a.EndTime+1,'2005')<=isnull(b.StartTime-1, '20051231')这个比较高手
create table #(StartTime datetime, EndTime datetime)
insert into #
select '2005-1-20 0:00:00', '2005-1-21 0:00:00' union all
select '2005-1-29 0:00:00', '2005-1-30 0:00:00' union all
select '2005-2-15 0:00:00', '2005-2-19 0:00:00' union all
select '2005-2-21 0:00:00', '2005-2-22 0:00:00'with cte as
(
select id=row_number()over(order by StartTime), * from # where 2005 between year(StartTime) and year(EndTime)
)
select isnull(a.EndTime+1,'2005')a, isnull(b.StartTime-1, '20051231')b from cte a full join cte b on a.id=b.id-1
where isnull(a.EndTime+1,'2005')<=isnull(b.StartTime-1, '20051231')