select sum(datediff(dd,StartDate,EndDate) from 表
select sum(datediff(d,StartDate,EndDate ))
from (
select distinct StartDate,EndDate
from tb
)T
select days=sum(datediff(dd,StartDate,EndDate))
from (select distinct * from tb) A
declare @t table(ID int,SDate datetime,EDate datetime)
insert into @t select 1,'2009-04-01','2009-05-01'
insert into @t select 2,'2009-04-01','2009-05-01'
insert into @t select 3,'2009-03-14','2009-04-12'
insert into @t select 4,'2009-06-01','2009-06-30'
select
a.SDate,min(b.EDate) as EDate
from
(select SDate from @t t where not exists(select 1 from @t where SDate<t.SDate and EDate>=t.SDate)) a,
(select EDate from @t t where not exists(select 1 from @t where SDate<=t.EDate and EDate>t.EDate)) b
where
a.SDate<=b.EDate
group by
a.SDate
/*
SDate EDate
------------------------------------------------------ ------------------------------------------------------
2009-03-14 00:00:00.000 2009-05-01 00:00:00.000
2009-06-01 00:00:00.000 2009-06-30 00:00:00.000
*/
declare @t table(ID int,SDate datetime,EDate datetime)
insert into @t select 1,'2009-04-01','2009-05-01'
insert into @t select 2,'2009-04-01','2009-05-01'
insert into @t select 3,'2009-03-14','2009-04-12'
insert into @t select 4,'2009-06-01','2009-06-30'
select
sum(n.Date) as Days
from
(select
datediff(dd,a.SDate,min(b.EDate)+1) as Date
from
(select SDate from @t t where not exists(select 1 from @t where SDate<t.SDate and EDate>=t.SDate)) a,
(select EDate from @t t where not exists(select 1 from @t where SDate<=t.EDate and EDate>t.EDate)) b
where
a.SDate<=b.EDate
group by
a.SDate) n
/*
Days
-----------
79
*/
declare @t1 table(id int,StartDate datetime,EndDate datetime)
insert @t1
select 1, '2009-4-1' , '2009-5-1' union all
select 2, '2009-4-1' , '2009-5-1' union all
select 3, '2009-3-14', '2009-4-12' union all
select 4, '2009-6-1' , '2009-6-30'
select days=sum(datediff(dd,StartDate,EndDate))
from (select distinct StartDate,EndDate from @t1) A
days
-----------
88
(1 行受影响)
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int,startdate datetime,enddate datetime)
go
insert into tb
select 1,'2009-4-1','2009-5-1' union all
select 2,'2009-4-1','2009-5-1' union all
select 3,'2009-3-14','2009-4-12' union all
select 4,'2009-6-1','2009-6-30'
go
select SUM(DATEDIFF(dd,startdate,enddate))
from(
select * from tb t where not exists
(select * from tb where id>t.id and DATEDIFF(DD,t.startdate,t.enddate)=DATEDIFF(DD,startdate,enddate))) t
/*------------
59
-------*/
if object_id('tb') is not null
drop table tb
go
create table tb(id int, startDate datetime,endDate datetime)
go
insert into tb
select 1, '2009-4-1', '2009-5-1' union all
select 2, '2009-4-1', '2009-5-1' union all
select 3, '2009-3-14', '2009-4-12' union all
select 4, '2009-6-1', '2009-6-30'
go
create table #(date datetime)
go
declare @num int
select @num = max(id) from tb
declare @i int
set @i = 1
while @i <= @num
begin
insert into #
select dateadd(day,b.number,a.startDate) from tb a ,master..spt_values b
where b.type = 'P' and dateadd(day,b.number,a.startDate)<=a.endDate and id = @i
set @i = @i+1
end
select count(1) as [时间总数] from (select distinct * from #)T
drop table tb,#
/*
79
*/
create table #temp(autoid int identity(1,1),startDate datetime, endDate datetime)
insert into #temp
select '2009-4-1','2009-5-1' union all
select '2009-4-1','2009-5-1' union all
select '2009-3-14','2009-4-12' union all
select '2009-6-1','2009-6-30'
autoid startDate endDate
----------- ----------------------- -----------------------
1 2009-04-01 00:00:00.000 2009-05-01 00:00:00.000
2 2009-04-01 00:00:00.000 2009-05-01 00:00:00.000
3 2009-03-14 00:00:00.000 2009-04-12 00:00:00.000
4 2009-06-01 00:00:00.000 2009-06-30 00:00:00.000
(4 行受影响)
----------
select sum(counts) from
(
select datediff(day,startDate, endDate) as counts from #temp
group by StartDate, endDate
) as a
-----------
88
(1 行受影响)
drop table #temp
