DECLARE @starttime DATETIME,@stoptime DATETIME
SET @starttime='2014-1-13'
SET @stoptime='2014-1-15'
SELECT 1.0*SUM(DATEDIFF(SECOND
,CASE WHEN @starttime>starttime THEN @starttime ELSE starttime END
,CASE WHEN @stoptime>stoptime THEN stoptime ELSE @stoptime END))
/DATEDIFF(SECOND,@starttime,@stoptime)
FROM TB
WHERE starttime<=@stoptime AND stoptime>=@starttime我觉得大概可以这样
SET @starttime='2014-1-13'
SET @stoptime='2014-1-15'
SELECT 1.0*SUM(DATEDIFF(SECOND
,CASE WHEN @starttime>starttime THEN @starttime ELSE starttime END
,CASE WHEN @stoptime>stoptime THEN stoptime ELSE @stoptime END))
/DATEDIFF(SECOND,@starttime,@stoptime)
FROM TB
WHERE starttime<=@stoptime AND stoptime>=@starttime我觉得大概可以这样
不会这么简单?SUM会把重叠的时间多次计算了吧?
SET @starttime='2014-1-1'
SET @stoptime='2014-1-9'--SQL2000 不能像高级版本那样方便
--中间步骤处理
DECLARE @Start TABLE(starttime DATETIME,RN INT IDENTITY)
INSERT INTO @Start
SELECT starttime FROM TB T
WHERE starttime<=@stoptime AND stoptime>=@starttime AND
NOT EXISTS(SELECT 1 FROM TB WHERE id<>T.id AND T.Starttime<=stoptime AND T.starttime>=starttime)
GROUP BY starttime ORDER BY starttimeDECLARE @End TABLE(stoptime DATETIME,RN INT IDENTITY)
INSERT INTO @End
SELECT stoptime FROM TB T
WHERE starttime<=@stoptime AND stoptime>=@starttime AND
NOT EXISTS(SELECT 1 FROM TB WHERE id<>T.id AND T.stoptime<=stoptime AND T.stoptime>=starttime)
GROUP BY stoptime ORDER BY stoptime
--中间步骤结束SELECT 1.0*SUM(DATEDIFF(SECOND
,CASE WHEN @starttime>starttime THEN @starttime ELSE starttime END
,CASE WHEN @stoptime>stoptime THEN stoptime ELSE @stoptime END))
/DATEDIFF(SECOND,@starttime,@stoptime)
FROM(
SELECT T1.starttime,T2.stoptime
FROM @Start T1 JOIN @End T2 ON T1.RN=T2.RN
)TB
--WHERE starttime<=@stoptime AND stoptime>=@starttime--条件放到前面可以提高效率
可以利用ROW_NUMBER将表start 表end要实现的功能写在派生表里去,实现整个功能一个语句完成