February 18, 2003 at 10:11 pm
Hi Tom,
I have got data in the following rows.
My objective is to update the CLOSE DATE column. The CLOSEDATE of a particular
ID
is the next OPENDATE of the same ID.
For example - in the data given below, for ID "02283ESWM001302" and QUEUE -
'AUS-HANDOFF',
the CLOSEDATE will be the OPENDATE for the same id in the second row.
ID OPENDATE CLOSEDT QUEUENAME
02283ESWM001302 24 Dec 2002 18:03:05 NULL AUS-HANDOFF
02283ESWM001302 24 Dec 2002 16:28:00 NULL AUS-VALIDATE
02283ESWM001302 10 Oct 2002 16:10:51 NULL AUS-UNCAT
02283ESWM001302 02 Jan 2003 20:20:02 NULL
02267ESWM000743 25 Sep 2002 18:13:19 NULL
My questions are
1) Can this be done without resorting to use a PL / SQL procedure?
2) If I use a cursor in a PL / SQL procedure, how do I update the column in the
previous row?
Cheers!
Abhijit
February 20, 2003 at 5:46 pm
Below are two examples to do what you want. Hope this is what you are looking for.
-- Example 1 only works id rows are returned in update in order by d, and opendate
create table x(d char(15), opendate datetime, closedt datetime, queuename char(20))
insert into x (d, opendate, queuename) values('02283ESWM001302','24 Dec 2002 18:03:05','AUS-HANDOFF')
insert into x (d, opendate, queuename) values('02283ESWM001302','24 Dec 2002 16:28:00','AUS-VALIDATE')
insert into x (d, opendate, queuename) values('02283ESWM001302','10 Oct 2002 16:10:51','AUS-UNCAT')
insert into x (d, opendate, queuename) values('02283ESWM001302','02 Jan 2003 20:20:02',' ')
insert into x (d, opendate, queuename) values('02267ESWM000743','25 Sep 2002 18:13:19',' ')
insert into x (d, opendate, queuename) values('02267ESWM000743','27 Sep 2002 18:13:19',' ')
select * from x
declare @x datetime
declare @i char(15)
set @x = ''
set @i = ''
update x
set
@x = case when @i <> d then null else @x end,
@x = closedt = @x ,
@x = opendate,
@i = d
select * from x
drop table x
--example 2 works regardless of order
create table x(d char(15), opendate datetime, closedt datetime, queuename char(20))
insert into x (d, opendate, queuename) values('02283ESWM001302','24 Dec 2002 18:03:05','AUS-HANDOFF')
insert into x (d, opendate, queuename) values('02267ESWM000743','25 Sep 2002 18:13:19',' ')
insert into x (d, opendate, queuename) values('02283ESWM001302','24 Dec 2002 16:28:00','AUS-VALIDATE')
insert into x (d, opendate, queuename) values('02283ESWM001302','10 Oct 2002 16:10:51','AUS-UNCAT')
insert into x (d, opendate, queuename) values('02283ESWM001302','02 Jan 2003 20:20:02',' ')
insert into x (d, opendate, queuename) values('02267ESWM000743','27 Sep 2002 18:13:19',' ')
select * from x
select * into #x from x order by d, opendate
declare @x datetime
declare @i char(15)
set @x = ''
set @i = ''
update #x
set
@x = case when @i <> d then null else @x end,
@x = closedt = @x ,
@x = opendate,
@i = d
update x
set closedt = #x.closedt
from #x where x.d=#x.d and x.opendate=#x.opendate
select * from x order by d, opendate
drop table #x
drop table x
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply