Add a computed column

Scenario: You have a StartDate column and an EndDate column, and an IsActive Column. An item should be ‘IsActive = true’ if the start date is before today and the EndDate is null or is after Today:

This script will add that ‘IsActive’ computed column to your table:
If today is after the startDate

ALTER TABLE dbo.World ADD IsActive AS  
 (isnull(CONVERT([bit],case when [StartDate]&lt=getutcdate()) then (1) else (0) end,(0)),(0)));

Or If today is between startDate and endDate

ALTER TABLE dbo.World ADD IsActive AS
(isnull(CONVERT([bit],case when [StartDate]<=getutcdate() AND ([EndDate] IS NULL OR [EndDate]>=getutcdate()) then (1) else (0) end,(0)),(0)))

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s