What is the easy way to do this?.

Transact-SQL

Visio User


My table structure...
enddt datetime(8)
status varchar(20)

when enddt time equal to server time.

I want to change status field to expired.

What is the easy way to do this .

enddt status

8/22/2005 7:00:00 PM expired

9/22/2005 8:30:00 PM expired

10/30/2005 7:30:00 PM live

11/22/2005 9:30:00 PM live

Top

Re: What is the easy way to do this?.

Chris Howarth


You could use a computed column, see below.

Chris

Code Block

IF OBJECT_ID('tempdb..#Test') IS NOT NULL
DROP TABLE #Test
GO

CREATE TABLE #Test
(
[TestID] INT IDENTITY(1, 1),
[enddt] DATETIME,
[status] AS CAST((CASE WHEN enddt <= GETDATE() THEN 'live' ELSE 'expired' END) AS VARCHAR(20))
)
GO

INSERT INTO #Test(enddt)
SELECT '20071210' UNION
SELECT '20071028'

SELECT *
FROM #Test
GO

IF OBJECT_ID('tempdb..#Test') IS NOT NULL
DROP TABLE #Test
GO

/*

TestID enddt status

1 2007-12-10 00:00:00.000 expired
2 2007-10-28 00:00:00.000 live
*/






Top

Re: What is the easy way to do this?.

Visio User

Data is present in the table...i can do this with data present .

[status] AS CAST((CASE WHEN enddt <= GETDATE() THEN 'live' ELSE 'expired' END) AS VARCHAR(20))





Top

Re: What is the easy way to do this?.

Visio User

in the formula field i need to enter this ...

CASE WHEN enddt <= GETDATE() THEN 'live' ELSE 'expired' END


Top

Re: What is the easy way to do this?.

Visio User

I want this to happen on existing data. Not while inserting time.

I want to trigger this, when enddt cross the server time status should change from live to expired.

This should happen automatic.

How can i do . Do i need to use trigger for that .

enddt status

8/22/2005 7:00:00 PM expired

9/22/2005 8:30:00 PM expired

10/30/2005 7:30:00 PM live

11/22/2005 9:30:00 PM live


Top

Re: What is the easy way to do this?.

DaleJ

Alter the table to add the new column


Code Block



CREATE TABLE #Test
(
[TestID] INT IDENTITY(1, 1),
[enddt] DATETIME,
)
GO

INSERT INTO #Test(enddt)
SELECT '20071210' UNION
SELECT '20071028'

SELECT *
FROM #Test
GO

ALTER TABLE #Test ADD [status] AS CAST((CASE WHEN enddt <= GETDATE() THEN 'live' ELSE 'expired' END) AS VARCHAR(20))
GO
SELECT *
FROM #Test
GO






Top

Re: What is the easy way to do this?.

Chris Howarth

Maybe I should have explained myself when I provided my suggestion...

A computed column is calculated every time the column is read so there's no need for you to use triggers to maintain the data. With the example I gave the data is not actually stored in SQL Server but the CASE statement is evaluated every time the column is read.

As DaleJ pointed out a computed column can be retrospectively added to a table without needing to update any existing rows - try it and see.

Read this for more info on computed columns:

http://msdn2.microsoft.com/en-us/library/ms186241.aspx

Chris





Top