Help with update statement

Hi guys,

I have the following sample data:

PaperID StatusID StatusDate StatusKey

0001 4566 2003-09-03 00:00:00.000 D

0001 4222 2003-09-03 00:00:00.000 C

0001 4132 2003-09-01 00:00:00.000 A

0002 4222 1999-04-14 00:00:00.000 C

0002 4132 1999-04-10 00:00:00.000 A

0003 4132 1986-08-03 00:00:00.000 A

0003 4566 1986-07-29 00:00:00.000 D

Now, if in the same paperID, there is a statusKy A and the status date is earlier than the other statusDate, i would like to change the other statusdate to be the same as the status date with the statusKy of 'A'. if there is a statusKy 'A', but the other Statusky contains dates that are earlier than the date in StatusKy 'A', then leave it as what it was.

The result i would want to see :

PaperID StatusID StatusDate StatusKey

0001 4566 2003-09-01 00:00:00.000 D

0001 4222 2003-09-01 00:00:00.000 C

0001 4132 2003-09-01 00:00:00.000 A

0002 4222 1999-04-10 00:00:00.000 C

0002 4132 1999-04-10 00:00:00.000 A

0003 4132 1986-08-03 00:00:00.000 A

0003 4566 1986-07-29 00:00:00.000 D

can you guys help me with this issue? i would appreciate it so much. thanks

You ought to be able to put together a pretty good 2-pass solution if you will update based on a derived table of the target. You MIGHT be able to put together a 1-pass solution using TSQL UPDATE extensions IF you need a faster solution.

|||Hi Kent, I am really a beginner in t-sql, would you precise what you mean by putting a 1-pass solution using tsql update extensions? thanks.|||

If you want to just view the data like this, you can do it like this:

Code Snippet

--including scripts like this will get you better responses
drop table test
go
create table Test
(
PaperID char(4),
StatusID char(4),
StatusDate smalldatetime,
StatusKey char(1),
Primary Key (PaperId, StatusId)
)
insert into Test
select '0001','4566','2003-09-03 00:00:00.000','D'
union all
select '0001','4222','2003-09-03 00:00:00.000','C'
union all
select '0001','4132','2003-09-01 00:00:00.000','A'
union all
select '0002','4222','1999-04-14 00:00:00.000','C'
union all
select '0002','4132','1999-04-10 00:00:00.000','A'
union all
select '0003','4132','1986-08-03 00:00:00.000','A'
union all
select '0003','4566','1986-07-29 00:00:00.000','D'
go

select Test.PaperId, Test.StatusId,
case when Astatus.StatusDate < Test.StatusDate
then Astatus.StatusDate
else Test.StatusDate
end as StatusDate,
StatusKey
from Test
join ( select PaperId, StatusId, statusDate
from Test
where StatusKey = 'A') as Astatus
on Test.PaperId = Astatus.PaperId

|||

Thank you so much, i really appreciate your help. you guys are awesome, thanks again.

Jul.

|||

Jul:

Sorry that I was unable to finish my response. I thought I had about 15 minutes that I could get you an answer but I have been really slammed with DB2 work lately. I should be able to finish my answer in the morning. What I mean by a 1-pass solution is that it only has to traverse the data of the table 1 time.

Kent

|||

If you are using SQL Server 2005 then you can use the query below instead which scans the data only once.

Code Snippet

select t.PaperId

, t.StatusId

, case when t.Status_A_Date < t.StatusDate then t.Status_A_Date else t.StatusDate end as StatusDate

, t.StatusKey
from (
select *, min(case StatusKey when 'A' then StatusDate end) over(partition by PaperId) as Status_A_Date
from Test
) as t;

|||NP Kent, thanks again for your contribution!!!! |||

Jul:

Here is an example of a 1-pass update that uses the TSQL extensions:

Code Snippet

create table dbo.mockup
( PaperID varchar(5),
StatusID integer,
StatusDate datetime,
StatusKey char(1),
)
go

create index mockup_UpdExt_Cvr
on dbo.mockup (PaperID, StatusKey, StatusDate)
go

insert into mockup
select '0001', 4566, '2003-09-03 00:00:00.000', 'D' union all
select '0001', 4222, '2003-09-03 00:00:00.000', 'C' union all
select '0001', 4132, '2003-09-01 00:00:00.000', 'A' union all
select '0002', 4222, '1999-04-14 00:00:00.000', 'C' union all
select '0002', 4132, '1999-04-10 00:00:00.000', 'A' union all
select '0003', 4132, '1986-08-03 00:00:00.000', 'A' union all
select '0003', 4566, '1986-07-29 00:00:00.000', 'D'

declare @.nextDate datetime

update mockup
set @.nextDate
= case when a.StatusKey = 'A' then a.statusDate
else @.nextDate
end,
statusDate = @.nextDate
from mockup a (index=mockup_UpdExt_Cvr)

select PaperID,
StatusID,
convert(varchar(10), statusDate, 101) as statusDate,
StatusKey
from mockup

/*
PaperID StatusID statusDate StatusKey
- -- -
0001 4566 09/01/2003 D
0001 4222 09/01/2003 C
0001 4132 09/01/2003 A
0002 4222 04/10/1999 C
0002 4132 04/10/1999 A
0003 4132 08/03/1986 A
0003 4566 08/03/1986 D
*/

Before going farther what I would suggest is that under normal circumstances it is probably better to use either Uma's or Louis' code rather than employ the TSQL update extension -- tend to use the update extensions sparingly.

I especially appreciate Uma's response because I keep forgetting about the use of the OVER( PARTITION BY ... ) clause being availabe with aggregate functions. Please stay after me until I get this right. It seems like OVER(ORDER BY ...) is not available for aggregates but only for the ranking functions; is that correct?

|||

Now, what if i would like to select only those that have statusdates that are later than statusdates with statusky 'A'?

Table1

PaperID StatusID StatusDate StatusKey

0001 4566 2003-09-03 00:00:00.000 D

0001 4222 2003-09-03 00:00:00.000 C

0001 4132 2003-09-01 00:00:00.000 A

0002 4222 1999-04-14 00:00:00.000 C

0002 4132 1999-04-10 00:00:00.000 A

0003 4132 1986-08-03 00:00:00.000 A

0003 4566 1986-07-29 00:00:00.000 D

And i would like to have a result of :

PaperID StatusID StatusDate StatusKey

0001 4566 2003-09-03 00:00:00.000 D

0001 4222 2003-09-03 00:00:00.000 C

0001 4132 2003-09-01 00:00:00.000 A

0002 4222 1999-04-14 00:00:00.000 C

0002 4132 1999-04-10 00:00:00.000 A

I do not want to select 0003 because it has the correct data (statusdate with statusky 'A' is later than the other status dates). additionally, if let say i have a paperID that contains a statusky of 'A' and a statusky of 'D' having the same dates, that would not be selected in the query.

What is the best query to use? thanks.