我在SQL Server中有一个表,其中包含用于不同作业的用户的持续时间.我需要计算用户的总体验数.
Declare @temp table(Id int, FromDate DATETIME, ToDate DATETIME) INSERT INTO @temp ( Id ,FromDate ,ToDate ) VALUES ( 1 , '2003-1-08 06:55:56' , '2005-5-08 06:55:56'), ( 2 , '2000-10-08 06:55:56' , '2008-7-08 06:55:56'), ( 3 , '2013-6-08 06:55:56' , '2015-1-08 06:55:56'), ( 4 , '2006-4-08 06:55:56' , '2011-3-08 06:55:56' ) SELECT * FROM @temp
我想计算经验总数;
Id FromDate ToDate Difference IN Months =================================================== 1 2003-01-08 2005-05-08 28 2 2000-10-08 2008-07-08 93 3 2013-06-08 2015-01-08 19 4 2006-04-08 2011-03-08 59
去除了像2003-2005这样的年份重叠在2000 - 2008年; 我有这样的事情:
Id FromDate ToDate Difference IN Months =================================================== 1 2000-10-08 2011-03-08 125 2 2013-06-08 2015-01-08 19
所以答案是125+19 = 144
几个月.请帮我找一个解决方案.
这里的语法是查找没有重叠FromDate和ToDate间隔的所有FromDate以及没有重叠FromDate和ToDate间隔的所有ToDate.根据日期值给他们一个rownumber并在rownumber上匹配它们:
;WITH CTE as ( SELECT min(Id) Id ,FromDate, row_number() over (ORDER BY FromDate) rn FROM @temp x WHERE not exists (SELECT * FROM @temp WHERE x.FromDate > FromDate and x.FromDate <= Todate) GROUP BY FromDate ), CTE2 as ( SELECT Max(Id) Id ,ToDate, row_number() over (ORDER BY ToDate) rn FROM @temp x WHERE not exists (SELECT * FROM @temp WHERE x.ToDate >= FromDate and x.ToDate < Todate) GROUP BY ToDate ) SELECT SUM(DateDiff(month, CTE.FromDate, CTE2.ToDate)) FROM CTE JOIN CTE2 ON CTE.rn = CTE2.rn
结果:
144