How to update date basing on another column date

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

How to update date basing on another column date

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-01-16 : 02:42:54

hello all,

i have a table in which
date of Eligibility Coverage END

2011-12-03 00:00:00.000 2012-12-03 00:00:00.000
out put should be lkike
2011-12-03 00:00:00.000 2012-3-03 00:00:00.000

i need to update another column basing on date of this column
with 3 month difference or 6 months diff or 1 year difference..basing on the date of Eligibility column

how can i do this??

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 03:24:22

Duplicate http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=182138

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-01-16 : 03:57:39

No chandu...here i asked about single update query basing one column to another column

i worked out this issue

Thanks

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 04:10:48

How sholud you provided solution... post the solution. It may help others too

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-01-16 : 05:13:44

i have a table in which
1)i need to generate random dates for one column called date of Eligibility

2) basing on the date of Eligibility column i need to update 2 nd column with difference of 3months,4monthsetc....;

my first query :

UPDATE UserInsuranceBenfitType
SET DateOfEligibility = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365), '2011-01-01')

my second query :
UPDATE UserInsuranceBenfitType_temp
SET CoverageEndsDate = dateADD(MM,3,DateOfEligibility)
where UserInsuranceBenfitTypeId between 1and 15000
UPDATE UserInsuranceBenfitType_temp
SET CoverageEndsDate = dateADD(MM,6,DateOfEligibility)
where UserInsuranceBenfitTypeId between 15000 and 25000

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 05:26:28

You can do 2nd update part as single UPDATE statement

UPDATE UserInsuranceBenfitType_temp
SET CoverageEndsDate =
CASE WHEN UserInsuranceBenfitTypeId between 1and 15000 THEN dateADD(MM,3,DateOfEligibility)
WHEN UserInsuranceBenfitTypeId between 15001 and 25000 THEN coverageEndsDate = dateADD(MM,6,DateOfEligibility)
END

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-01-16 : 07:03:01

thanks chandu

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 07:25:04