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

• Akina: I think [Hours] output field in 1st query is excess, and it is safe to remove it and add DISTINCT to remove duplicated records from output.
• Randi Vertongen: @Akina thanks for the feedback, if I remove the hours column and add distinct, the first query would be the same as the second query in terms of resultset right? That is why I added the [Hours] explicitly to the first one, as I would prefer the second one in terms of performance if that is the case.
• Akina: the first query would be the same as the second query in terms of resultset right? They will produce the same resultset, but 2nd variant must be more fast (maybe slightly but nevertheless).