0

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.

3

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

  • 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. – Akina Oct 30 at 13:33
  • @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. – Randi Vertongen Oct 30 at 13:42
  • 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). – Akina Oct 30 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.