Given: Given a Microsoft SQL database table
Log with multiple columns including these important ones:
id (primary key),
code (an integer that can take multiple values representing status changes),
lastupdated (a datetime field)...
What I need:
I need to add a computed column
ActiveDate which stores the exact first time when the code changed to 10 (i.e. an active status). As the status keep[s changing in future, this column must maintain the same value as the exact time it went active (thus keeping the active datetime record persistently). This timestamp value should initially begin with a NULL.
My approach I want the activedate field to automatically store the datetime at which the status code becomes 10, but when the status changes again, I want it to remain the same. Since I can't reference a calculated column from a calculated column, I created a user defined function to fetch the current value of activedate and use that whenever the status code is not 10.
- I can't make modifications to the Db or to columns (other than the new columns I can add).
- This T-SQL script must be idempotent such that it can be run multiple times at anytime in the production pipeline without losing or damaging data.
Here is what I tried.
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name=N'ActiveDate' AND OBJECT_ID = OBJECT_ID(N'[dbo].[Log]')) /* First, create a dummy ActiveDate column since the user-defined function below needs it */ ALTER TABLE [dbo].[Log] ADD ActiveDate DATETIME NULL IF OBJECT_ID('UDF_GetActiveDate', 'FN') IS NOT NULL DROP FUNCTION UDF_GetActiveDate GO /* Function to grab the datetime when status goes active, otherwise leave it unchanged */ CREATE FUNCTION UDF_GetActiveDate(@ID INT, @code INT) RETURNS DATETIME WITH SCHEMABINDING AS BEGIN DECLARE @statusDate DATETIME SELECT @statusDate = CASE WHEN (@code = 10) THEN [lastupdated] ELSE (SELECT [ActiveDate] from [dbo].[Log] WHERE id=@ID) END FROM [dbo].[Log] WHERE id=@ID RETURN @statusDate END GO /* Rename the dummy ActiveDate column so that we can be allowed to create the computed one */ EXEC sp_rename '[dbo].[Log].ActiveDate', 'ActiveDateTemp', 'COLUMN'; /* Computed column for ActiveDate */ ALTER TABLE [dbo].[Log] ADD ActiveDate AS ( [dbo].UDF_GetActiveDate([id],[code]) ) PERSISTED NOT NULL /* Delete the dummy ActiveDate column */ ALTER TABLE [dbo].[Log] DROP COLUMN ActiveDateTemp; print ('Successfully added ActiveDate column to Log table') GO
What I get: The following errors
- [dbo].[Log].ActiveDate cannot be renamed because the object participates in enforced dependencies.
- Column names in each table must be unique. Column name 'ActiveDate' in table 'dbo.Log' is specified more than once.
Is my approach wrong? Or is there a better way to achieve the same result? Please help.