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

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

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);

ВЫХОД:

введите здесь описание изображения

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

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

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


sql sql-server rollup
person Sunny    schedule 04.12.2018    source источник

Ответы (3)


arrow_upward
0
arrow_downward

У меня было подобное требование, и я решил его следующим образом:

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
person Derviş Kayımbaşıoğlu    schedule 04.12.2018
comment
Я получаю: Msg 241, уровень 16, состояние 1, строка 168 Преобразование не удалось при преобразовании даты и/или времени из символьной строки. так как в моем случае часы могут превышать двухзначную длину. вроде 120:00. - person Sunny; 06.12.2018
comment
пожалуйста, проверьте мой обновленный ответ. если это работает, пожалуйста, примите это как ответ. благодарю вас :) - person Derviş Kayımbaşıoğlu; 06.12.2018
comment
Спасибо Сделано :-) - person Sunny; 07.12.2018

arrow_upward
1
arrow_downward

Я бы сначала исправил ваш тип данных, время должно храниться как 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
person Larnu    schedule 04.12.2018
comment
Используйте секунды вместо минут для более точного результата. - person Salman A; 04.12.2018
comment
@SalmanA мы не знаем, что '05:56' в образце ОП. Это предполагает, что их данные равны mm:ss. Я предположил hh:mm (согласно комментарию в SQL), но у нас нет возможности узнать это без их ведома. - person Larnu; 04.12.2018
comment
Еще один вопрос, поскольку я использую тип varchar, можем ли мы сделать это, используя тип varchar, только путем преобразования? - person Sunny; 04.12.2018
comment
@Sunny, почему бы не исправить тип данных в таблице? Это, безусловно, лучший вариант. В противном случае вы могли бы преобразовать значение [time] в time (о, какая ирония, столбец с именем [time] не является time) в выражении, да. - person Larnu; 04.12.2018
comment
На самом деле это временная таблица в моем SP, после вычисления полей из другого набора таблиц я получаю эти значения. - person Sunny; 04.12.2018
comment
@Larnu В результате мне нужны только часы и минуты (ЧЧ: ММ), как я могу это сделать? В настоящее время результат идет с секундами. - person Sunny; 06.12.2018
comment
@Larnu Я получаю: сообщение 241, уровень 16, состояние 1, строка 168 Преобразование не удалось при преобразовании даты и/или времени из строки символов. так как в моем случае часы могут превышать двухзначную длину. вроде 120:00. - person Sunny; 06.12.2018

arrow_upward
0
arrow_downward

Итак, беглый обзор кода. Старайтесь не использовать тип данных 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);

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

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

RTRIM(**450**/60) + ':' + RIGHT('0' + RTRIM(**450**%60),2)
person GDS    schedule 04.12.2018
comment
ах да, забыл убрать ‹br› после SELECT, это можно сделать самому - person Pawel Czapski; 04.12.2018
comment
только что сделал, спасибо, пытаюсь адаптироваться к форматированию здесь. - person GDS; 04.12.2018