Solved

date update in sql server 2005

Posted on 2009-04-14
21
269 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 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