Derived column based on another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Derived column based on another table

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2012-10-30 : 13:14:34

I want to set a derived column for expiration date using a different table. I tried:

SELECT DATEADD(mi, (CAST(SELECT column from table where expiration = 'Time')as int),getdate())

But it didn't work. The column that I am pulling from is a varchar field, is what I am doing even possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 13:33:43

[code]
SELECT DATEADD(mi,column,getdate()) from table where expiration = 'Time'
[/code]
provided column has a valid numerical value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Johnph
Posting Yak Master

103 Posts

Posted - 2012-10-30 : 13:40:54

Getting: Argument data type varchar is invalid for argument 2 of dateadd function. The column returns the value of "10" as varchar when I do the select clause.

Also... is there such thing as a derived column that doesnt change ? for example getdate() as a dervived column but each time I reselect the table it gives me getdate() instead of the first time it grabs the date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 13:44:39

try


SELECT DATEADD(mi,CAST(column AS int),getdate()) from table where expiration = 'Time'

is there such thing as a derived column that doesnt change ?

for that you need to use table variable or local variable depending on whether you need to store single value or resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Johnph
Posting Yak Master

103 Posts

Posted - 2012-10-30 : 13:52:40

Thanks Visakh, that seem to have done it. You're right, so if I added a constraint like this:

ALTER TABLE [test1] ADD CONSTRAINT [test1] DEFAULT (DATEADD(mi,CAST(column AS int),getdate()) from table where expiration = 'Time') FOR [ExpireTime]
ALTER TABLE [test2] ADD CONSTRAINT [test2] DEFAULT (getdate()) FOR [CreateTime]

That answer my issues right?

**EDIT**

Hmm, I want to be able to use the query you gave me as a default value for a column and the first alter table didn't work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 14:36:15

it wont work. you can make constraint based on an expression like this

I cant understand what exactly you're trying to do here. where will you be using these values for? can you elaborate on exact scenario?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Johnph
Posting Yak Master

103 Posts

Posted - 2012-10-30 : 15:04:19

Creation - Datetime
Expiration - I need this to auto populate with (DATEADD(mi,CAST(column AS int),getdate()) from table where expiration = 'Time')

Basically the expiration date is controlled by another table.

Example:

Create date is "12-12-12 3:00"
Expiration time should be "12-12-12 3:30"

Now the +30 minutes is a query from another table, if I were to change the data in the column to 45, it would look like this:

Create date is "12-12-12 3:00"
Expiration time should be "12-12-12 3:45"

I hope I am making more sense. I've read somewhere that I can do some type of trigger, but I have never done one before.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 15:09:47

so does other table have always single record for expiration? and will value be changing over time?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Johnph
Posting Yak Master

103 Posts

Posted - 2012-10-31 : 19:43:39

Hey sorry for the late response, I managed to get this done using a trigger to set the expiration time based on another column. When someone adds data it auto-populates.

Thanks so much for the help Visakh!@

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources