求1SQL语句，给一时间，求在时间段中，一共几个月

求一SQL语句，给一时间，求在时间段中，一共几个月

SQL code

CREATE TABLE test(    startTime SMALLDATETIME,    endTime SMALLDATETIME    )INSERT INTO test(startTime,    endTime)SELECT '2012-01-01','2012-03-01' UNION ALL SELECT '2012-04-10','2012-07-10' UNION ALLSELECT '2012-08-01','2012-10-01'--如给一时间：‘2012-06-01’,则在第一条数据中2个月+第二条数据2个月共4个月

SQL code

CREATE TABLE test(    startTime SMALLDATETIME,    endTime SMALLDATETIME    )INSERT INTO test(startTime,    endTime)SELECT '2012-01-01','2012-03-01' UNION ALL SELECT '2012-04-10','2012-07-10' UNION ALLSELECT '2012-08-01','2012-10-01'--如给一时间：‘2012-06-01’,则在第一条数据中2个月+第二条数据2个月共4个月select sum( case when datediff(mm,startTime,'2012-06-01') > datediff(mm,startTime,endTime) then datediff(mm,startTime,endTime) else datediff(mm,startTime,'2012-06-01')end ) Num from test where startTime <='2012-06-01'/*Num-----------4(1 行受影响)*/
[解决办法]
SQL code
SELECT COUNT(starttime)+COUNT(endtime) FROM test WHERE  startTime<='2012-06-01' /* ----------- 4  (1 行受影响)  */
[解决办法]
SQL code
declare @dt datetimeset @dt='2012-06-01'select sum(datediff(mm,starttime,case when endtime>@dt then @dt else endtime end))from testwhere starttime<=@dt
[解决办法]
SQL code
USE TestGO--IF OBJECT_ID('test')IS NOT NULL--    DROP TABLE testCREATE TABLE test(    startTime SMALLDATETIME,    endTime SMALLDATETIME    )INSERT INTO test(startTime,    endTime)SELECT '2012-01-01','2012-03-01' UNION ALL SELECT '2012-04-10','2012-07-10' UNION ALLSELECT '2012-08-01','2012-10-01'DECLARE @Date DATETIMESET @Date='2012-06-01'SELECT SUM(DATEDIFF(mm,starttime,CASE WHEN endTime>@Date THEN @Date ELSE endTime END)) FROM Test WHERE startTime<@Date