0
IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table 
GO
Create Table #Table
(
  CorrectionDate datetime,
  Origdesc nvarchar(50),
  Newdesc nvarchar(50),
  Total int
)

Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-08-01 00:00:00.000','RMAWAR','NTF',193)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-08-01 00:00:00.000','NTF','RMAWAR',108)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-08-01 00:00:00.000','RMAWAR','Sub Abuse',86)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-08-01 00:00:00.000','NTF','Other',71)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-09-01 00:00:00.000','Other','Out of Warranty',129)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-09-01 00:00:00.000','RMAWAR','Sub Abuse',	103)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-09-01 00:00:00.000','Cosmetic','NTF',91)
Insert Into #Table (CorrectionDate, Origdesc,Newdesc,Total) Values ('2011-09-01 00:00:00.000','Scrap','Out of Warranty',35)

Derived Result:

Correctiondate|OrigDesc|NewDesc|Total
----------------------------------------------------
2011-08-01 00:00:00.000 |RMAWAR |NTF | 193
2011-08-01 00:00:00.000 |NTF |RMAWAR | 108
2011-08-01 00:00:00.000 |RMAWAR |Sub Abuse |86
2011-09-01 00:00:00.000 |Other |Out of Warranty| 129
2011-09-01 00:00:00.000 |RMAWAR |Sub Abuse| 103
2011-09-01 00:00:00.000 |Cosmetic|NTF |91


Thanks,

JOV

Edited by Ezzaral: Added code tags. Please use them to format any code that you post.

2
Contributors
3
Replies
4
Views
5 Years
Discussion Span
Last Post by adam_k
0

Thank you very much Adam_K.

BTw, here is the query.

Select
tmp.Origdesc,
tmp.Newdesc,
tmp.CorrectionMth,
tmp.Total
From (Select ROW_NUMBER() over (partition by correctionMth order by total desc) as rowno,
Origdesc,
Newdesc,
CorrectionMth,
Total
From #VpData2 ) tmp
where tmp.rowno <=1

0

Please be kind enough to mark this thread as solved, if your problem has been solved.
Thanks

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.