Member Avatar for jovillanuev 
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 because: Added code tags. Please use them to format any code that you post.
  • 2 Contributors
  • 3 Replies
  • 91 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by adam_k
Member Avatar for jovillanuev

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

Member Avatar for adam_k

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.