CODEWITHSUNDEEP
sqlsql-server
John
John

Reputation: 145

How best to sum 2 columns and update 3rd column with sum?

I am looking for the best way to add 2 or more columns in a SQL Server table and update another column with there sum.

Yes, I know this is a dumb thing to do and calculations should be done at time of transaction but I am modifying an existing table where the data in a column now needs to be more detailed but numerous processes will still use the column value.

For example, a column name is TotalDailyMiles and numerous processes access and use that field. Now more detail is needed. 2 columns need to be added to the table TotalAMMiles and TotalPMMiles. These 2 columns will sum to the existing column. Changing all the processes that access the TotalDailyMiles column to use the 2 new columns instead is not an option. The data for the new columns in old records does not exist so the value for columns holding the sum of the 2 new columns cannot be based on the 2 new columns in old records because in old records the new column values will be 0, or maybe null but I'm leaning toward 0 so I can set the new columns as Not Null.

I'm thinking of using a trigger to update the column holding the sum based on the new columns changing but I'm hoping one of you smart people have a better option.

Upvotes: 1

Views: 2115

Answers (1)

ErikE
ErikE

Reputation: 50241

How about treating the existing column as its own value (which will be 0 in future rows), adding the two new columns, and then creating a calculated column with the same name as the old Total? Something like this (I'm assuming a data type of decimal(7, 2) but of course use what you have, though I hope it's not float):

EXEC sp_rename 'dbo.Miles.TotalDailyMiles', 'DailyMiles';
ALTER TABLE dbo.Miles ADD COLUMN AMMiles decimal(7, 2) NOT NULL
   CONSTRAINT DF_Miles_AMMiles DEFAULT (0);
ALTER TABLE dbo.Miles ADD COLUMN PMMiles decimal(7, 2) NOT NULL
   CONSTRAINT DF_Miles_PMMiles DEFAULT (0);
ALTER TABLE dbo.Miles ADD COLUMN TotalDailyMiles
   AS (DailyMiles + AMMiles + PMMiles) PERSISTED;

Some possible housekeeping that might be needed on the DailyMiles column, too:

-- if not already NOT NULL
ALTER TABLE dbo.Miles ALTER COLUMN AMMiles decimal(7, 2) NOT NULL;
-- if not already defaulting to 0
ALTER TABLE dbo.Miles ADD
   CONSTRAINT DF_Miles_DailyMiles DEFAULT (0) FOR DailyMiles;

You could additionally add a constraint that either DailyMiles must be 0, or AMMiles and PMMiles must both be 0:

ALTER TABLE dbo.Miles ADD CONSTRAINT CK_Miles_DailyOrAMPM
   CHECK (DailyMiles = 0 OR (AMMiles = 0 AND PMMiles = 0));

As long as consumers of the data don't try to update the TotalDailyMiles column, you've solved your problem handily.

Upvotes: 3

Related Questions