• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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
0
sas77
Asked:
sas77
  • 9
  • 6
  • 4
  • +2
1 Solution
 
Mark WillsTopic AdvisorCommented:
Might be best as a trigger, unless you want to always attach the additional column everytime and everywhere it can be updated...

0
 
chapmandewCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you want a trigger that creates the new row automatically?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
dportasCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
dportasCommented:
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
 
sas77Author Commented:
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
 
sas77Author Commented:
mark can you write the code so that it will be easy for me to understand.

Thanks.
0
 
dportasCommented:
>> 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
 
Mark WillsTopic AdvisorCommented:
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
 
sas77Author Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
dportasCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
sas77Author Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
sas77Author Commented:
Thanks Mark,

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

0
 
Mark WillsTopic AdvisorCommented:
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
 
sas77Author Commented:
Great Ideas.Thank you
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 9
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now