iUpdate column wth variables from another column

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

General SQL Server Forums

New to SQL Server Programming

iUpdate column wth variables from another column

Author

Topic

Peter Horne
Starting Member

1 Post

Posted - 2012-11-19 : 01:30:35

Hi all,
first post here, bear with me!
I have a table with fields of InspectionDate, Type & DueDate. I need to update DueDate field(automatically??)with different date values depending on the values in InspectDate & Type fields.

Eg InspectDate 1/1/2012, Type (A), DueDate would be (InspectDate+1)
InspectDate 1/1/2012, Type (B) , Duedate would be(InpsectDate+20)
Etc, etc.

Any help appreciated.

Thanks
Pete

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-19 : 01:57:11

so are there are different rules for different type values? then you need to make DueDate a computed field based on rules using a CASE WHEN expression

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-19 : 02:11:44

Just this script is based on your information..


CREATE TABLE Test1 (InspectionDate date, [Type] char(1), 
DueDate AS case when InspectionDate = '1/1/2012' and [type] = 'A' THEN dateadd(DD, 1, InspectionDate)
				when InspectionDate = '1/1/2012' and [type] = 'B' THEN dateadd(DD, 20, InspectionDate)
				ELSE InspectionDate end  )
--I need to update DueDate field(automatically??)with different date values depending on the values in InspectDate & Type fields.
INSERT INTO Test1(InspectionDate, Type) VALUES ('1/1/2012', 'A'), ('1/1/2012', 'B')
GO
SELECT * FROM Test1
GO
DROP TABLE Test1

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-19 : 02:31:03

another way though not recommended is to create an INSERT,UPDATE trigger on table to do this computation

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources