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.