Solved

date update in sql server 2005

Posted on 2009-04-14
21
266 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24141981
so, you want a trigger that creates the new row automatically?
0
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
query returning everything 11 68
SQL Help joining two tables 7 33
sqlseverexpress 2008 agent xps question 1 11
sql query Help 12 17
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now