Sql-server – How to update a SET statement so I can use the MIN() function

sql-server-2005t-sql

I have an existing UPDATE table SET statement that was working until one of the Drivers used two different trucks in his/her shift, so now what I need to do is modify the UPDATE statement so I can use the MIN() function on a few columns. When I tried to add the MIN() function to the statement as it exists I received the error

An aggregate may not appear in the set list of an UPDATE statement."

Here is my existing code for the UPDATE statement:

UPDATE #tmpDriverTime
SET @StartPlus8 =   CASE 
                        WHEN  ((CONVERT(int, DTE.StrtTm) + 800) > 2400) AND (CONVERT(INT, DTE.StartBreak) < 1600) THEN ((CONVERT(INT, DTE.StrtTm) + 800) - 2400)
                        ELSE (CONVERT(INT, DTE.StrtTm) + 800)
                    END,
    WorkDay1Break = CASE
                        WHEN ((WorkDay1 <=8) AND (WorkDay1Break = 0)) THEN 2 --Does not need a break
                        WHEN ((WorkDay1 > 8 AND DTE.StartBreak IS NULL) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours with no break
                        WHEN (@StartPlus8 < CONVERT(INT, DTE.StartBreak) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours before the first break
                        ELSE 0
                    END
FROM dbo.spr_DriverTimeEntry AS DTE
WHERE DTE.DrvrID = #tmpDriverTime.EmployeeNo
AND CONVERT(VARCHAR(12), DTE.dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND DTE.PyrllID IN (
    1,
    222,
    1013,
    1014
);

Example inputs:

 Dtwrkd  Driver  StrTm  StartBreak
 1/1/11    1     0430   NULL 
 1/1/11    1     1200   1430

Goal results:

 Dtwrkd  Driver  StrTm  StartBreak
 1/1/11    1     0430   1430

I am not able to change the table schema so please don't come in here tell me I should redesign the entire then so it is setup properly, I don't have that luxury.

Code I tried that produced the error:

UPDATE #tmpDriverTime
SET @StartPlus8 =   CASE 
                        WHEN  ((CONVERT(int, MIN(DTE.StrtTm)) + 800) > 2400) AND (CONVERT(INT, DTE.MIN(StartBreak)) < 1600) THEN ((CONVERT(INT, MIN(DTE.StrtTm)) + 800) - 2400)
                        ELSE (CONVERT(INT, MIN(DTE.StrtTm)) + 800)
                    END,
    WorkDay1Break = CASE
                        WHEN ((WorkDay1 <=8) AND (WorkDay1Break = 0)) THEN 2 --Does not need a break
                        WHEN ((WorkDay1 > 8 AND MIN(DTE.StartBreak) IS NULL) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours with no break
                        WHEN (@StartPlus8 < CONVERT(INT, MIN(DTE.StartBreak)) AND (WorkDay1Break = 0)) THEN 1 --Worked more then 8 hours before the first break
                        ELSE 0
                    END
FROM dbo.spr_DriverTimeEntry AS DTE
WHERE DTE.DrvrID = #tmpDriverTime.EmployeeNo
AND CONVERT(VARCHAR(12), DTE.dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND DTE.PyrllID IN (
    1,
    222,
    1013,
    1014
);

Best Answer

I'm not going to try to understand or re-write your entire query, but the typical form to get around this parser limitation is to calculate the aggregates in a CTE. The basic idea:

;WITH cte(id, dt) AS 
(
  -- simplifying here, you'll need to with your logic
  SELECT DrvrID, MIN(datetimecolumn) 
  FROM dbo.spr_DriverTimeEntry
  -- WHERE ...
  GROUP BY DrvrID
)
UPDATE t
  SET t.StartPlus8 = cte.dt
  FROM #tmpDriverTime AS t
  INNER JOIN cte
  ON cte.id = t.EmployeeNo;

Why create the #temp table with a different column name (EmployeeNo) instead of what it really represents (DrvrID)?

Are you updating a #table or setting a variable? Pick one.

Also try to avoid converting a date to a string to see if they're on the same day - this is by far one of the least efficient ways to do so.

Related Question