How to update only 1 of the rows if there are duplicates while using aggregate functions

user_2048219 0
Viewed 48

I am trying to create a stored procedure that adds lunch hours based on SUM(TotalTime). My dilemma is on the records that are duplicates. It adds lunch twice. See the screenshot below. I would like it to add in the lunch only once per day per employee based on the total hours worked. Here is what I have so far.

CREATE TABLE TimeCards 
(
    [Counter] [int] IDENTITY(1,1) NOT NULL,
    EmployeeID nvarchar(50),
    Date DateTime,
    Lunch decimal(10,2),
    TotalTime decimal(10,2)
)

INSERT INTO TimeCards (EmployeeID, Date, Lunch, TotalTime)
VALUES ('1001', '2021-02-04 00:00:00.000', Null, 8) 
, ('136', '2021-02-04 00:00:00.000', Null, 4)
, ('136', '2021-02-04 00:00:00.000', Null, 4)
, ('418', '2021-02-04 00:00:00.000', Null, 5)
, ('418', '2021-02-04 00:00:00.000', Null, 5)
, ('511', '2021-02-04 00:00:00.000', Null, 5)
, ('511', '2021-02-04 00:00:00.000', Null, 6)

UPDATE TimeCards 
SET Lunch = CASE 
               WHEN SUMTotalTime BETWEEN 5.501 AND 11 THEN .5
               WHEN SUMTotalTime BETWEEN 11.01 AND 16 THEN 1
               WHEN SUMTotalTime >= 16.01 THEN 1.5
            END 
FROM 
    (SELECT 
         tc.EmployeeID, Date, MAX(totaltime) AS totaltime,  
         SUM(TotalTime) AS SUMTotalTime 
     FROM 
         TimeCards tc
     GROUP BY 
         tc.EmployeeID, Date 
     HAVING 
         SUM(Totaltime) > 5.5) grouped 
 WHERE 
     TimeCards.totaltime = grouped.totaltime 
     AND TimeCards.Date = grouped.Date 
     AND TimeCards.EmployeeID = grouped.EmployeeID 
     AND grouped.SUMTotalTime > 5.5 
     AND Lunch IS NULL

Current results of the query and Intended Results

sql-server-2014 sql-server sql
2 Answers

Seems like what you want is an updatable CTE:

WITH CTE AS(
    SELECT TC.EmployeeID,
           TC.[Date], --If this is a date, why is it a datetime?
           TC.Lunch,
           TC.TotalTime,
           SUM(TC.TotalTime) OVER (PARTITION BY TC.EmployeeID, TC.Date) AS TotalHours,
           ROW_NUMBER() OVER (PARTITION BY TC.EmployeeID, TC.Date ORDER BY Counter DESC) AS RN
    FROM dbo.TimeCards TC)
UPDATE CTE
SET Lunch = CASE WHEN TotalHours BETWEEN 5.501 AND 11 THEN .5
                 WHEN TotalHours BETWEEN 11.01 AND 16 THEN 1
                 WHEN TotalHours >= 16.01 THEN 1.5
            END 
WHERE TotalHours > 5.5
  AND Lunch IS NULL
  AND RN = 1;

db<>fiddle

If someone could already have a lunch added, and it might not be the "last" row, you could check that the "Max Lunch" is NULL instead:

WITH CTE AS(
    SELECT TC.EmployeeID,
           TC.[Date], --If this is a date, why is it a datetime?
           TC.Lunch,
           TC.TotalTime,
           SUM(TC.TotalTime) OVER (PARTITION BY TC.EmployeeID, TC.Date) AS TotalHours,
           MAX(Lunch) OVER (PARTITION BY TC.EmployeeID, TC.Date) AS MaxLunch,
           ROW_NUMBER() OVER (PARTITION BY TC.EmployeeID, TC.Date ORDER BY Counter DESC) AS RN
    FROM dbo.TimeCards TC)
UPDATE CTE
SET Lunch = CASE WHEN TotalHours BETWEEN 5.501 AND 11 THEN .5
                 WHEN TotalHours BETWEEN 11.01 AND 16 THEN 1
                 WHEN TotalHours >= 16.01 THEN 1.5
            END 
WHERE TotalHours > 5.5
  AND MaxLunch IS NULL
  AND RN = 1;

db<>fiddle

user_20299830

Just for completeness, as I think I irritated Larnu and he doesn't seem to want to accomodate you comments ;)

Just change the ROW_NUMBER() part of his answer, and make it so that it picks the "rows-to-be-updated" based on whether the Lunch value is NULL before checking the counter value...

WITH CTE AS(
    SELECT TC.EmployeeID,
           TC.[Date], --If this is a date, why is it a datetime?
           TC.Lunch,
           TC.TotalTime,
           SUM(TC.TotalTime)
             OVER (PARTITION BY TC.EmployeeID, TC.Date
                  )
                    AS TotalHours,
           ROW_NUMBER()
             OVER (PARTITION BY TC.EmployeeID, TC.Date
                   ORDER BY CASE WHEN Lunch IS NULL THEN 1 ELSE 0 END, Counter DESC
                  )
                    AS RN
    FROM dbo.TimeCards TC)
UPDATE CTE
SET Lunch = CASE WHEN TotalHours BETWEEN 5.501 AND 11 THEN .5
                 WHEN TotalHours BETWEEN 11.01 AND 16 THEN 1
                 WHEN TotalHours >= 16.01 THEN 1.5
            END 
WHERE TotalHours > 5.5
  AND RN = 1;

Also, I strongly recommend NOT using BETWEEN on continuous values such as decimals, dates, etc. In your scenario CASE will match the first true condition, so you can just do this...

SET Lunch = CASE WHEN TotalHours > 16.0 THEN 1.5
                 WHEN TotalHours > 11.0 THEN 1
                 WHEN TotalHours >  5.5 THEN .5

Even if you do need ranges, I recommend this...

SET Lunch = CASE WHEN TotalHours >  5.5 AND TotalHours <= 11.0 THEN .5
                 WHEN TotalHours > 11.0 AND TotalHours <= 16.0 THEN 1
                 WHEN TotalHours > 16.0                        THEN 1.5

It's slightly longer, but more explicit and more robust.

Dems_533410
Related