Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
infodemers
Posting Yak Master
183 Posts |
 Posted - 2013-03-21 : 13:40:53
|
| Hi,I have to do some clean up on a regular basis on a table because of the bad architecture of the DB.I want to assign the value for the Remark field to the item with the newest effective date only. So in the following example the value for the remark would be blank or null in second row.Cust|Name|EffectiveDate|Remark|514-371|MTRL|2013-03-21|CHG POI|514-371|MTRL|2001-10-04|CHG POI|Thanks! |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2013-03-21 : 14:04:14
|
| create table #booya (Cust varchar(50), name varchar(50), EffectiveDate datetime, Remark varchar(50))insert into #booyaselect '514-371', 'MTRL','2013-03-21','CHG POI'unionselect '514-371','MTRL','2001-10-04','CHG POI'select Cust, EffectiveDate, RANK() OVER (PARTITION BY Cust ORDER BY EffectiveDate DESC) AS ranktology from #booya;with cteMango(Cust, EffectiveDate, ranktology)as(select Cust, EffectiveDate, RANK() OVER (PARTITION BY Cust ORDER BY EffectiveDate DESC) AS ranktology from #booya ) update tgt set tgt.Remark = case when src.ranktology <> 1 then null else tgt.Remark end from cteMango src join #booya tgt on src.Cust = tgt.Cust select * from #booya |
 |
|
|
infodemers
Posting Yak Master
183 Posts |
Posted - 2013-03-21 : 16:22:34
|
| Hi Yosiasz,Thanks for the snippet. I could make it using some pieces of your code. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2013-03-21 : 17:05:29
|
| sure thing.<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
|
|
|
|
|
Remove duplicate values