How to count multiple occurences on the same date

Question

my question is related to the table down below, where I have to count multiple occurences of "Hours" if they happen on the same "Date" by the same person.

CREATE TABLE [dbo].[Time](
    [GUIDTime] [uniqueidentifier] NOT NULL,
    [GUIDEmployee] [uniqueidentifier] NULL,
    [Date] [datetime] NOT NULL,
    [Hours] [decimal](18, 2) NOT NULL,
    [Info] [varchar](256) NULL,
    [TaskDescription] [varchar](256) NULL,

---

 GUIDEmployee   Date            Hours
    1       2012-03-02          2.00
    1       2012-03-02          4.50
    1       2012-03-19          1.50
    1       2012-03-19          1.50
    1       2012-03-12          2.00
    1       2012-03-16          4.50

The problem is that in order to make an overtime calculation work, I need to count the hours worked. Normally people only enter it by the end of the day (7h,8h,9h) and thats it, but there are cases like above, where employees enter their hours seperately

2012-03-02   2.00
2012-03-02   4.50

and that makes the calculation invalid.

The outcome should look like

 GUIDEmployee   Date             Hours
    1        2012-03-02          6.50
    1        2012-03-19          3.00
    1        2012-03-12          2.00
    1        2012-03-16          4.50

Favorably I'd like to have another column in the Time table with the calculation of the "whole hours worked per day". I tried Count distinct and group by and tried searching for other ways to solve this issue through calculations in a seperate view but sadly to no avail.

Answer 1

You might want one of these queries:

#1 Extra column with window function:

SELECT [GuidEmployee],
       [Date],
       [Hours], 
       SUM([Hours]) OVER(PARTITION BY GuidEmployee,[Date]) as total_hours
FROM [dbo].[Time];

Result

GuidEmployee    Date                    Hours   total_hours
1               2012-03-02 00:00:00.000 2.00    6.50
1               2012-03-02 00:00:00.000 4.50    6.50
1               2012-03-12 00:00:00.000 2.00    2.00
1               2012-03-16 00:00:00.000 4.50    4.50
1               2012-03-19 00:00:00.000 1.50    3.00
1               2012-03-19 00:00:00.000 1.50    3.00

---

#2 Standard sum, aggreggate by employee guid & date

SELECT [GuidEmployee],
       [Date], 
       SUM([Hours]) as total_hours
FROM [dbo].[Time]
GROUP BY [GuidEmployee],[Date];

Result:

GuidEmployee    Date                    total_hours
1               2012-03-02 00:00:00.000 6.50
1               2012-03-12 00:00:00.000 2.00
1               2012-03-16 00:00:00.000 4.50
1               2012-03-19 00:00:00.000 3.00

DB<>Fiddle