?
Solved

date update in sql server 2005

Posted on 2009-04-14
21
Medium Priority
?
273 Views
Last Modified: 2012-05-06
HI,

I HAVE  THE FOLLOWING TABLE STRUCTURE.

HISTORYKEY
ORDERID
ORDRNAME
STARTING DATE
ENDDATE
VERSION

WHEN I FIRST POPULATE THIS SARTDATE  WILLBE  GETDATE() AND ENDDATE WILL BE 2050-01-01, WHEN ANY CHANGE DETECTED IN THE ROW IT SHOULD GIVE ENDATE AS THAT DATE WHEN THE RECORD CHANGE. ATTACHE IS THE FORMAT WHAT I NEED,I JUST NEED UPDATE STATEMENT.
SAMPLE.DOC.doc
0
Comment
Question by:sas77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 4
  • +2
21 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24141960
Might be best as a trigger, unless you want to always attach the additional column everytime and everywhere it can be updated...

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24141974
create trigger tr_triggername
on tablename
after update
as
begin
update t
set enddate = getdate()
from tablename t
join inserted i on t.historykey = i.historykey
end
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24141981
so, you want a trigger that creates the new row automatically?
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 22

Expert Comment

by:dportas
ID: 24141984
To create a new row when data changes you need an INSERT statement, not an UPDATE.

To UPDATE the ExpirationDate is straightforward:

UPDATE the SET ExpirationDate = CURRENT_TIMESTAMP WHERE... ;

or, if you have a default set to CURRENT_TIMESTAMPE (GETDATE) then do:

UPDATE the SET ExpirationDate = DEFAULT WHERE... ;
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24142194
Yeah, having re-read and looked at your document, and considered the different naming conventions, then do not see the need for a new row as dportas and angelIII mention, more so just the update of the enddate (or is that expirationdate).  Though given the name "expirationdate" then it does kind of imply that the row has bnow expired and a new one might now takes it's place - kind of like an audit trail.

Could you please clarify ?

If just an update then dportas shows that above, But consider the update syntax is: update <tablename> set <columnname> = getdate() where <row selection>
and that is pretty much the same in the trigger except we have the system generated tables of inserted (and deleted for the old values) showing the affected rows, and that is where chapmandew has the inner join on inserted for the <row selection>

Both the trigger (fired automatically when there is a change) or the explicit update is only operating on the affected row.
0
 
LVL 22

Expert Comment

by:dportas
ID: 24142375
I think a trigger is way too much code just to update the datetime on a row. Do it in your UPDATE statement would be my advice. That's what UPDATEs are for after all! :)
0
 

Author Comment

by:sas77
ID: 24142576
Yes mark  i need to update  the expiration date as getdate when a record change on that day.

for example if i update
like
update table
set expirationdate = getdate()
where version = 0

then it will update with current date for all records which have version 0.

i need to update expiration date only for that particular row and particular date.
0
 

Author Comment

by:sas77
ID: 24142637
mark can you write the code so that it will be easy for me to understand.

Thanks.
0
 
LVL 22

Expert Comment

by:dportas
ID: 24142676
>> i need to update expiration date only for that particular row and particular date
What particular row? You didn't specify any row except where version = 0.

Please show us some sample data and show what end result you want.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24142750
Well, absolutely best way (as far as I am concerned) is the good old fashioned trigger on the databse. chapmandew has already shown you, and happy to repeat / discuss in more detail.

Assuming a table name of "mytable"... The biggest challenge is making sure you can uniquely identify the row. It might be an identity column, a primary key, or several columns needed to uniquely identify the row. And that is something you will need to elaborate on / take care of.


create trigger trg_mytable on mytable
after update
as
begin
   update mytable set enddate = getdate()
   from mytable
   inner join inserted on mytable.historykey = inserted.historykey              -- assume historykey can identify a unique row.
end
0
 

Author Comment

by:sas77
ID: 24142832
I am doing this in SSIS so i want to add on script component and insert this update statement into that.Attach file will give good overview of what i need exactly

Thanks
SAMPLE2.doc
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24142869
A trigger is like a stored procedure. The main difference is it is automatically fired off / executed with data activity on a table - that is where the
"after update" bit comes into it. Can also have inserted and deleted. The trigger is an "after" trigger - no choice on that - the data has been written to the table already. Now the rows that were affected are held in a system generated and managed table called "inserted" - it is a replica of the live table but just with those affected rows.

Lets look at the trigger line by line :

create trigger trg_mytable on mytable                                      -- give the trigger a good name, normally prefix your objects with something meaningful and obvious
after update                                                               -- used to say which data event this trigger is going to be run
as
begin                                                                                                           
   if not update(enddate)                                                  -- if the data being updated is not enddate (ie if someone is explictly updating it then we shouldnt)
      update mytable set enddate = getdate()                               -- the actual update of the column in the live table
      from mytable
      inner join inserted on mytable.historykey = inserted.historykey      -- assume historykey can identify a unique row.
end

Open in new window

0
 
LVL 22

Expert Comment

by:dportas
ID: 24142879
You can use the SSIS SCD Wizard to build the components you need.
http://blogs.conchango.com/jamiethomson/archive/2005/06/06/1543.aspx
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24142918
Aaaahhhh, it is like an audit trail !!  Do you need the new row added with the current one expiring ?

Need more details as to how the data relates - what is the "unique" part of the row that we must propogate ?
0
 

Author Comment

by:sas77
ID: 24142948
Mark.

In my second attachment for ID 39544 LEVEL changes to 4 then it should reflect the changes on date columns to identify the records with new row added.

I Think i confused everybody but i need the exact requirement as shown in sample2.doc attachment
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24143073
OK, was confused, and now just not sure :) :)

So, lets spell it out...

1) you are adding a new row
2) that row effectively replaces the old row
3) the old row needs to be "expired" by updating the enddate
4) how do we identify the new row as a replacement for the old one - is it simply the same productid ?

In ssis after the import of the new rows, would create an execute sql step and put in the following update...


update mytable set enddate = new.begindate
from mytable
inner join mytable new on new.productid = mytable.productid  and  new.key > mytable.key and new.begindate < mytable.enddate


That should go through and make all enddates the same as begindate for the same productid when there is a newer productid with a different key value (one could argue that n.key <> mytable.key) . It does assume that the column "key" is always incrementing, like an identity.
0
 

Author Comment

by:sas77
ID: 24143190
Thanks Mark,

But how about if it a row gets updated multiple times.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24143248
Not sure what you are saying... Can there be multiple "new" rows for the same product ?

in which case if you just need the max(key) which "should" be the latest entry, or possibly the max(begindate) then might want a subquery :


update mytable set enddate = new.begindate
from mytable
inner join (select top 1 productid, key, begindate from mytable order by key desc) new on new.productid = mytable.productid  and  new.key > mytable.key and new.begindate < mytable.enddate
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24143264
Nah... that wont do it...

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 24143298
Can there be multiple "new" rows for the same product ? Can you please elaborate / show example of what you mean ?

if there are multiples and you just need the max(key) which "should" be the latest entry, or possibly the max(begindate) then might want a subquery :


update mytable set enddate = new.begindate
from mytable
inner join (select productid, max(key) as max_key from mytable group by productid) p on p.productid = mytable.productid and p.max_key > mytable.key
inner join mytable new on new.key = p.max_key and new.productid = p.productid and new.begindate < mytable.enddate
0
 

Author Closing Comment

by:sas77
ID: 31570121
Great Ideas.Thank you
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question