27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
IF NOT OBJECT_ID(N'Tempdb..#T') IS NULL
DROP TABLE #T
GO
CREATE TABLE #T
(
StartTime DATETIME,
EndTime DATETIME
)
INSERT #T
SELECT '2017/7/10 17:12:25',
'2017/7/10 19:36:18' UNION ALL
SELECT '2017/7/10 10:33:42',
'2017/7/11 08:21:15' UNION ALL
SELECT '2017/7/9 13:32:40',
'2017/7/10 15:40:53'
GO
--测试数据结束
DECLARE @mintime DATETIME,
@maxtime DATETIME
SELECT @mintime = MIN(t.StartTime),
@maxtime = MAX(t.EndTime)
FROM #T AS t;
WITH days AS (
SELECT CONVERT(DATE, @mintime) AS dd
UNION ALL
SELECT DATEADD(DAY, 1, d.dd) AS dd
FROM days AS d
WHERE d.dd < CONVERT(DATE, @maxtime)
)
SELECT d.dd,
AVG(
CASE
WHEN CONVERT(DATE, t.StartTime) < d.dd THEN CASE
WHEN CONVERT(DATE, t.EndTime) = d.dd THEN DATEDIFF(hour, d.dd, t.EndTime)
WHEN CONVERT(DATE, t.EndTime) > d.dd THEN 24
END
WHEN CONVERT(DATE, t.StartTime) = d.dd THEN CASE
WHEN CONVERT(DATE, t.EndTime) = d.dd THEN DATEDIFF(hour, t.StartTime, t.EndTime)
WHEN CONVERT(DATE, t.EndTime) > d.dd THEN DATEDIFF(hour, t.StartTime, DATEADD(DAY, 1, d.dd))
END
END
) AS avghour
FROM #T AS t
JOIN days AS d
ON 1 = 1
GROUP BY
d.dd