SQL - вычислить сумму часов с использованием накопительного пакета

Question

Я рассчитываю получить сумму всех часов в качестве итога в последнем ряду. Я использую следующий запрос для получения результата:

create table time_test (type varchar(10),time varchar(10))
insert into time_test values ('A','01:25')
insert into time_test values ('B','02:30')
insert into time_test values ('C','05:56')
insert into time_test values ('D','00:50')

--select * from time_test

SELECT
  type = ISNULL(type, 'Total'),
  time = substring((cast(sum(((cast(substring (time,2,1) as decimal)*60 + cast(substring (time,4,2) as decimal))/60)) as varchar(10))),1,2)+':' + cast((cast((round(((cast((((cast((substring((cast((sum(((cast(substring (time,2,1) as decimal)*60 + cast(substring (time,4,2) as decimal))/60))) as varchar(10))),4,2)) as int))*60)) as decimal)/100)),0)) as int)) as varchar(10))
FROM time_test
GROUP BY ROLLUP(type);

ВЫХОД:

Как вы можете видеть, время идет не так, как надо, но общий расчет работает нормально.

Проблема: Пожалуйста, дайте мне знать, где я работаю неправильно во время отображения данных.

Заранее спасибо.

Answer 1

У меня было похожее требование, и я решил его так:

select type, RTRIM(time/60) + ':' + RIGHT('0' + RTRIM(time%60),2) from
( 
    select type= ISNULL(type, 'Total'),
      time= SUM(DATEDIFF(MINUTE, '0:00:00', time))
    from time_test
    GROUP BY ROLLUP(type)
) x

Я считаю, что это более понятно и легче отследить

type    time
A       1:25
B       2:30
C       5:56
D       0:50
Total   10:41

Изменить: обновлен запрос дляhour can be more then 24 hours

select type, RTRIM(time/60) + ':' + RIGHT('0' + RTRIM(time%60),2) from
( 
    select type= ISNULL(type, 'Total'),
      time= SUM(DATEDIFF(MINUTE, '0:00:00', 
        DATEADD(day, SUBSTRING(time,0,CHARINDEX(':',time,0)) / 24,
        DATEADD(hour, SUBSTRING(time,0,CHARINDEX(':',time,0)) % 24, 
        DATEADD(minute,SUBSTRING(time,CHARINDEX(':',time)+1,LEN(time)) +0, 0)) )))
    from time_test
    GROUP BY ROLLUP(type)
) x

Пример результата:

A       1:25
B       2:30
C       5:56
D       70:50
Total   80:41

Answer 2

Я бы сначала исправил ваш тип данных, время должно быть сохранено как timeне varchar, Затем, после того как вы исправили свой тип данных, вы можете обрабатывать свои данные так, как они есть (время).

USE Sandbox;
GO

CREATE TABLE dbo.time_test ([type] varchar(10),
                            [time] time);
INSERT INTO dbo.time_test
VALUES ('A', '01:25'); --Assumes hh:mm
INSERT INTO dbo.time_test
VALUES ('B', '02:30');
INSERT INTO dbo.time_test
VALUES ('C', '05:56');
INSERT INTO dbo.time_test
VALUES ('D', '00:50');
GO

SELECT ISNULL([type],'Total') AS [Type],
       DATEADD(MINUTE,SUM(DATEDIFF(MINUTE,'00:00',[time])),CONVERT(time(0),'00:00')) AS [Time]
FROM dbo.time_test
GROUP BY [type] WITH ROLLUP;

GO

DROP TABLE dbo.time_test;

Это возвращает:

Type       Time
---------- ----------------
A          01:25:00
B          02:30:00
C          05:56:00
D          00:50:00
Total      10:41:00

Answer 3

Итак, быстрый обзор кода, постарайтесь не использовать тип данных VARCHAR для хранения даты и / или времени.

ниже то, что я сделал:

create table time_test (type varchar(10),time1 time)
insert into time_test values ('A','00:01:30')
insert into time_test values ('B','00:02:30')
insert into time_test values ('C','00:01:00')
insert into time_test values ('D','00:02:30')

SELECT
  type = ISNULL(type, 'Total'),
  seconds_worked = SUM(DATEDIFF(SECOND, '00:00', time1))
  FROM time_test
GROUP BY ROLLUP(type);

вы увидите, что общее количество составляет 450, что, если вы рассчитываете вручную из предоставленных данных, кажется правильным, то есть общее время составляет 450 секунд.

Вы можете использовать приведенную ниже формулу, чтобы вернуть время в минутах и ​​секундах:

RTRIM(**450**/60) + ':' + RIGHT('0' + RTRIM(**450**%60),2)