Wyliczanie różnicy pomiędzy datami (czas) do formatu HOUR:MINUTE:SEC oraz zapytanie agregujące

Cześć

Mam problem do rozgryzienia z funkcjami czasu i agregacją czasu.

Musze wyliczyć czas nadgodzin pracowników w ramach 1 danego dnia roboczego.
Nadgodzinny liczą się przed rozpoczęciem pracy z grafiku oraz po zakończeniu. Łącząc tabele niestety joinowanie dubluje mi wartości przepracowanego czasu z grafiku.
Musze zatem w podzapytaniu w tabeli jak na rysunku zaagregować czas nadogodzin per dany dzień, gdzie dzień to wyciągnieta data z pola date_from oraz joinować po polu real_id do tabeli z grafikiem.

Czas musze pokazywać w formacie HOURSEC, ale funkcją DATEDIFF nie potrafię tego wyliczyć w formacie tak jak na screenie bo albo w minutach liczy albo w godiznach albo sekundach.. :(

Czy pomógłby mi ktoś ze składnią tak aby:

1. Wyliczyć czas nadgodzin w formacie HOURSEC
2. przygotowanie takiego grupowania aby się ładnie zsumowało tak na na screeenie z outputem

Z góry dzięki za wskazówki

Daniel

W podzapytaniu cte wylicz sobie czas pracy i czas nadgodzin. Potem to pokaż w głównym select. Powinno to pójść.
Join dubluje wartości wtedy kiedy użyjesz nieprawidłowego joina.

Pokaż tego datediff ciężko powiedzieć dlaczego Ci nie działa. Może być tak że źle go zapisujesz a może masz różne typy.

https://stackoverflow.com/questions/14445600/datediff-in-hhmmss-format

FORMAT(CONVERT(TIME, REQUEST.date_to - REQUEST.date_from), N'hh:mm:ss') AS [Duration]

To remedium - dzięki woolfik!

Teraz jeszcze grupowanie i bede w domu :D

@woolfik:
sum(FORMAT(CONVERT(TIME, REQUEST.date_to - REQUEST.date_from), N'hh:mm:ss')) AS [Nadgodziny zatwierdzone]

wywala mi:
Msg 8117, Level 16, State 1, Line 7
Operand data type nvarchar is invalid for sum operator.

Nie mam pod ręką MSSQL ale FORMAT zwraca Ci varchar jak chcesz sumować to musisz najpierw pyknać funkcję SUM, a dopiero to co Ci wyjdzie przeformatowac na to co chcesz :)

@woolfik:
nie chce mi tego policzyć :/
sum(REQUEST.date_to - REQUEST.date_from) AS [Nadgodziny zatwierdzone]

Doszedłem do tego momentu:
select
pracownik.nazwisko + ' ' +pracownik.imie as [Pracownik],
real_id,
CONVERT(DATE, date_from, 120) as [DATA REQUESTU],
status,
type,
REQUEST.date_from, REQUEST.date_to, FORMAT(CONVERT(TIME, REQUEST.date_to - REQUEST.date_from), N'hh:mm:ss') AS [Nadgodziny zatwierdzone]

from
REQUEST -
INNER JOIN
pracownik on pracownik.id = REQUEST.employee_id
where REQUEST.status <> 0 AND REQUEST.status <> 2 AND REQUEST.type != 4 AND REQUEST.type != 5

1. Używaj formatowania kodu będzie lepiej widać
2. Jak chcesz dać SUM na jakiejś kolumnie to wszystkie pozostałe (chyba prócz MySQL) powinny być uwzględnione w klauzuli group by natomiast @kate87 podała Ci jak to można sprytnie przy użyciu CTE ogarnąć jeśli nie wiesz co to CTE to tu przyklad:
   https://www.sqlpedia.pl/cte-common-table-expressions/
3. Co do sumowania daty / czasu to proponuję zliczyć czas w minutach (można w sekundach ale nie wiem czy jest sens) wyciągnąć to do CTE z ID pracownika i potem zrobić select z tabeli + join z CTE wyciągająć dla ID operatora sumę nadgodzin. Przykład na sumowanie czasu z cte:
   https://stackoverflow.com/questions/9725732/how-to-sum-up-time-field-in-sql-server
4. Jak chcesz gotowca to polecam SQLFiddle gdzie można wprowadzić strukturę, dane i przykładowe zapytanie aby inni mieli na czym pracować.

Czekaj czekaj bo się zakrecilismy. Ten błąd oznacza tylko tyle że nie możesz na formacie tekstowym zrobić sumy. Musisz po prostu wrzucić convert lub cast przed sumą. Dlatego najpierw w cte doprowadź to sobie do takiego formatu jaki potrzebujesz strzelam że to musi być int jeśli chcesz robić sumę, a potem w select robisz sobie magię z sumowaniem i wyliczaniem