1

I have a column that is used for calculations only. It displays either a 0 or a positive number which represents vacation time. In our Time table it is entered as a negative number and I convert it to a positive number for calculations. I need that positive value to go into the NonWorkHrs column for each entry by an employee. It needs to be the same value for all records within a user ID.

I have tried using case statements and select max within a sub-query

update DME
set NonWorkHrs = 
(
 select max(VacationHours)
 from DME b
 where useruid = b.useruid
 and useruid in (1,2,3)
 and NonWorkHrsHolder > 0
)
where useruid in (1,2,3)

I also tried a case statement

update DME
set NonWorkHrs =
(case 
  when (VacationHours > 0)
  then (VacationHours)
 --no else statement is need. All rows should have the same value
 end
 ) 
where useruid in (1,2,3)

I convert the negative TimeEntered values to positive values in the VacationHours column. The NonWorkHrs column is used to in calculations to determine the actual work hours.

The expected result is

Useruid UserFullName    NonWorkHrs  VacationHours   TimeEntered
1       Jane Doe         8             8             -8
1       Jane Doe         8             0              10
1       Jane Doe         8             0              12
2       John Doe         18            18            -18
2       John Doe         18            0              23
3       Bob Builder      16            16            -16
3       Bob Builder      16            0              40

The actual result is 

Useruid UserFullName    NonWorkHrs  VacationHours   TimeEntered
1       Jane Doe          18           8             -8
1       Jane Doe          18           0              10
1       Jane Doe          18           0              12
2       John Doe          18           18            -18
2       John Doe          18           0              23
3       Bob Builder       18           16            -16
3       Bob Builder       18           0              40
mcedave
  • 15
  • 6

3 Answers3

0

This query should work, try it.

DECLARE @DME TABLE
(
Useruid int,
UserFullName varchar(200),
NonWorkHrs  int,
VacationHours   int,
TimeEntered int
)

INSERT INTO @DME
VALUES
(1,'Jane Doe',18,8,-8),
(1,'Jane Doe',18,0,10),
(1,'Jane Doe',18,0,12),
(2,'John Doe',18,18,-18),
(2,'John Doe',18,0,23),
(3,'Bob Builder',18,16,-16),
(3,'Bob Builder',18,0,40)

SELECT d.* FROM @DME d

    UPDATE @DME
        SET NonWorkHrs = T2.VacationHours
    FROM @DME T1
    INNER JOIN (SELECT Max(VacationHours) AS VacationHours, Useruid FROM @DME GROUP BY Useruid) AS T2 
    ON T2.Useruid = T1.Useruid


SELECT d.* FROM @DME d
Hasan Mahmood
  • 978
  • 7
  • 10
0

Using CTE:

  ;WITH vacationsPerUser as (
      SELECT userId, max(VacationHours) as maxVacationHours
      FROM DME 
      WHERE useruid in (1,2,3)
        and NonWorkHrsHolder > 0
      GROUP BY userId
     )
  Update b
    SET b.NonWorkHrs = ISNULL(vacationsPerUser.maxVacationHours, 0)
    FROM DME b 
    LEFT JOIN vacationsPerUser
         ON vacationsPerUser.UserId = b.UserId
    WHERE b.useruid in (1,2,3);
Renat
  • 7,718
  • 2
  • 20
  • 34
  • The CTE worked beautifully Renat. Thank you so much. I guess I need to spend more time understanding CTEs :-) – mcedave Apr 18 '19 at 14:01
  • Just wanted to add I am able to use this CTE to update other columns in a similar fashion. Thanks again – mcedave Apr 18 '19 at 14:31
0

Or this (gives same as your expected result) 

UPDATE a
    SET NonWorkHrs = ABS(sub.minNegativeTime)
FROM DME a
INNER JOIN (
    SELECT useruid, MIN(TimeEntered) minNegativeTime
    FROM DME b
    WHERE b.TimeEntered < 0
    GROUP BY Useruid
) sub ON sub.Useruid = a.Useruid
Frode
  • 3,325
  • 1
  • 22
  • 32