22,209
社区成员
发帖
与我相关
我的任务
分享
-- params:startTime = '2017-01-01', endTime = '2018-08-01'
select COUNT(1) * 100 / 6
, DATEADD(HOUR, hs, @startTime) as tm
from (
select hs = DATEDIFF(HOUR, startTime, CreateTime)
from tbName
where CreateTime > startTime
and CreateTime < @endTime) as t
group by hs
ALTER PROCEDURE [dbo].[gethour]
@a DATETIME,
@b DATETIME,
@c NCHAR(50)
AS
BEGIN
DECLARE @xiaoshi INT,
@chushu FLOAT,
@num INT;
SET @chushu = 6;
SET @xiaoshi = DATEDIFF(HOUR, @a, @b);
SET @num = 0;
WHILE @num < @xiaoshi
BEGIN
SELECT (CAST(CONVERT( DECIMAL(18, 2),
100 * CAST(
(
SELECT COUNT(*)
FROM [g_zdcjl].[dbo].[zdcjl_data]
WHERE DATEDIFF(HH, Dtime, @a) = 0
AND 系统 = @c
) AS FLOAT) / @chushu
) AS VARCHAR) + '%'
) AS 百分比,
@a AS 时间,
@c AS 点位;
SET @a = DATEADD(HOUR, 1, @a);
SET @num = @num + 1;
END;
END;