Link to home
Start Free TrialLog in
Avatar of sas77
sas77

asked on

date update in sql server 2005

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
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Might be best as a trigger, unless you want to always attach the additional column everytime and everywhere it can be updated...

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
so, you want a trigger that creates the new row automatically?
Avatar of dportas
dportas

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... ;
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.
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! :)
Avatar of sas77

ASKER

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.
Avatar of sas77

ASKER

mark can you write the code so that it will be easy for me to understand.

Thanks.
>> 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.
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
Avatar of sas77

ASKER

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

You can use the SSIS SCD Wizard to build the components you need.
http://blogs.conchango.com/jamiethomson/archive/2005/06/06/1543.aspx
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 ?
Avatar of sas77

ASKER

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
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.
Avatar of sas77

ASKER

Thanks Mark,

But how about if it a row gets updated multiple times.
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
Nah... that wont do it...

ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sas77

ASKER

Great Ideas.Thank you