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.
