Q:

T-SQL How to set modification date without Trigger only if other values actually changed

My overall goal is to update the [StandardUnitCost] field only on "parts" that need to be updated while also updating the [DateUpdated] field only on those same lines. I am using a tool that syncs the data by matching up to the [DateUpdated] column only, so it is important that it only changes on lines that the [StandardUnitCost] has also updated on. It is important that both happen, and only on the same rows. If the [StandardUnitCost] field will be updated with the same value that it currently has, then the value has not changed and the [DateUpdated] field should not be updated.
I currently have this as two separate update statements and need help combining them.
Update [mas_wgd].[dbo].[CI_Item]
  Set dateupdated = CASE
   WHEN StandardUnitCost < AverageUnitCost then convert (date, GETDATE())
   WHEN (AverageUnitCost + 2) >= 22.0
    AND standardunitcost > (AverageUnitCost + 2.000000) then convert (date, GETDATE())
   When StandardUnitCost < 22.000000
    And StandardUnitCost > 0 then convert (date, GETDATE())
  Else dateupdated
end
Where ProductLine IN ('A010', 'A020', 'A030', 'A040', 'A050', 'A060', 'A070', 'A080',
      'A090', 'A100', 'A110', 'A120', 'A130', 'A130', 'A140', 'A150', 'A200', 'A250',
      'A300', 'A350', 'A400', 'A450', 'A500', 'A550', 'A600', 'AGNC', 'C010', 'C020',
      'C030', 'C040', 'C050', 'C060', 'C070', 'C080', 'C090', 'C100', 'C110', 'C120',
      'C130', 'C130', 'C140', 'C150', 'C200', 'C250', 'C300', 'C350', 'C400', 'C450',
      'C500', 'C550', 'C600', 'CGNC')

Update [mas_wgd].[dbo].[CI_Item]
 Set Standardunitcost = CASE
  WHEN (AverageUnitCost between 0.010000 and 22.000000) Then  22.00000 
  WHEN AverageUnitCost > 22.000000 then AverageUnitCost + 2.000000
 Else StandardUnitCost 
end
Where ProductLine IN ('A010', 'A020', 'A030', 'A040', 'A050', 'A060', 'A070', 'A080',
      'A090', 'A100', 'A110', 'A120', 'A130', 'A130', 'A140', 'A150', 'A200', 'A250',
      'A300', 'A350', 'A400', 'A450', 'A500', 'A550', 'A600', 'AGNC', 'C010', 'C020',
      'C030', 'C040', 'C050', 'C060', 'C070', 'C080', 'C090', 'C100', 'C110', 'C120',
      'C130', 'C130', 'C140', 'C150', 'C200', 'C250', 'C300', 'C350', 'C400', 'C450',
      'C500', 'C550', 'C600', 'CGNC')

A:

Doing this via a CTE will allow you to specify the formula once and use it to trigger the update on both fields:
SET NOCOUNT ON;
SET ANSI_NULLS ON;

DECLARE @Test TABLE (ID INT NOT NULL IDENTITY(1, 1),
                     AverageUnitCost INT NULL,
                     StandardUnitCost INT NULL,
                     ModifiedDate DATETIME,
                     WasUpdated BIT NULL);
INSERT INTO @Test VALUES (5, 8, GETDATE(), 0);
INSERT INTO @Test VALUES (15, 11, GETDATE(), 0);
INSERT INTO @Test VALUES (12, 35, GETDATE(), 0);
INSERT INTO @Test VALUES (22, 47, GETDATE(), 0);

SELECT * FROM @Test;

;WITH cte AS
(
  SELECT tmp.ID,
         tmp.StandardUnitCost,
         tmp.ModifiedDate,
         tmp.WasUpdated,
         CASE WHEN tmp.AverageUnitCost BETWEEN 10 AND 20 THEN 22
              WHEN tmp.AverageUnitCost > 20 THEN (tmp.AverageUnitCost + 2)
              ELSE tmp.StandardUnitCost
         END AS [NewValue]
  FROM @Test tmp
  --WHERE ProductLine IN ('A010'...) -- this is part of the real table so uncomment
)
UPDATE tab
SET    tab.StandardUnitCost = tab.NewValue,
       tab.ModifiedDate = GETDATE(),
       tab.WasUpdated = 1 -- only here to clearly indicate that the row was updated
FROM   cte tab
WHERE  tab.StandardUnitCost <> tab.NewValue;

SELECT * FROM @Test;

The [WasUpdated] field is only there because the statement runs fast enough where there might not be a difference in value for the [ModifiedDate] field on rows that were updated.

