inserting & adjusting existing dates SP or trigger

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 2005 Forums

Transact-SQL (2005)

inserting & adjusting existing dates SP or trigger

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2010-03-31 : 18:23:49

Inserting a new record
ID=1, neweffDate=1/7/10,newexpDate=NULL

update the previous if expdate > neweffDate. (expDate = newEffDate)
if newExpDate IS NULL and there is a recod with effdate > newEffDate (update newExpDate = effdate closest to the neweffdate)
if newExpDate IS NOT NULL and there is a recod with effdate < newExpDate (update newExpDate = effdate closest to the neweffdate)

tbl_Zones ( sample data )
ID effDate expDate
1 1/1/10 1/5/10
1 1/5/10 1/10/10 <---Update expDate = 1/7/10
<---Insert 1 1/7/10 NULL <---Update expDate = 1/1/11
1 1/1/11 1/5/11
1 1/5/11 NULL

pls help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-31 : 18:24:51

What is your question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2010-04-01 : 08:58:00

how can I accomplish this with a stored procedure or trigger?
I dont know how to find the closest next date, update records with a value from a record.

I need help constructing the stored proc or trigger.

thanks

quote:
Originally posted by tkizer

What is your question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-01 : 09:41:40

Hi,

Using the trigger you can do this way:

create table Test
(id int,
effdate datetime,
expdate datetime)

create trigger trg_test
on Test
after insert
as
Begin

Declare @CurDate datetime
Declare @lastDate datetime

Select @CurDate = effdate from inserted
select @lastdate = max(test.expdate) from test inner join inserted on test.id = inserted.id and test.effdate <> @CurDate

print @lastdate

update Test set expdate = dateadd(dd,-1,@CurDate) -- '2010-2-23'
From Test t inner join inserted i on t.id = i.id and t.expdate = @lastDate

end

insert into Test
Values (1,'2010-01-01','2010-01-31')

insert into Test
Values (1,'2010-01-15','2010-02-28')

insert into Test
Values (2,'2010-01-15','2010-02-28')

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2010-04-01 : 15:24:54

very nice this definatley got me to my solution!

Much appreciated!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources