Eliminate the rows - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Eliminate the rows

Author

Topic

ultimate_senthil
Starting Member

13 Posts

Posted - 2009-07-31 : 13:52:06

HI,

I have the following query. which returns the below results. i have to get only two records based on the maximum of date.In the Result,charges code TLU Repeated three times.i have to compress as the three TLU rows in to one based on the maximum of TODate (in the result 12/12/2009 is maximum).

SELECT distinct C.ChargeCode, CONVERT(varchar,CM.FromDate,103) as FromDate,Convert(varchar,CM.ToDate,103) as ToDate From QMS_Tbl_CustomClearanceTariffMaster CM
INNER JOIN QMS_Tbl_CustomClearanceTariffDetail CD ON CM.CustomClearanceTariffID = CD.CustomClearanceTariffID
INNER JOIN ERP_Tbl_ChargeCode C ON CD.ChargeCodeID = C.ChargeCodeId
INNER JOIN ERP_Tbl_ChargeCodeRelation CR ON C.ChargeCodeId = CR.ChargeCodeId
INNER JOIN ERP_Tbl_ChargeCodeSet CS ON CR.ChargeCodeSetId = CS.ChargeCodeSetId

WHERE CD.Mode = @Mode and CD.PortType = @PortType and ((CM.FromDate BETWEEN convert(DateTime, @FromDate ,103)
AND convert(DateTime, @ToDate ,103)) OR (CM.ToDate BETWEEN convert(DateTime, @FromDate ,103) AND convert(DateTime, @ToDate ,103)) OR (convert(DateTime, @FromDate ,103) BETWEEN CM.FromDate AND CM.ToDate) OR (convert(DateTime, @ToDate ,103) BETWEEN CM.FromDate AND CM.ToDate))

RESULT
-------

ChargeCode FromDate ToDate
---------- ----------- --------
BOE 12/12/2009 12/12/2009
TLU 12/12/2009 12/12/2009
TLU 16/08/2009 17/08/2009
TLU 31/07/2009 31/07/2009

senthilkumar

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-07-31 : 15:20:26

[code]
Select ChargeCode,FromDate,ToDate
from
(SELECT C.ChargeCode, CONVERT(varchar,CM.FromDate,103) as FromDate,Convert(varchar,CM.ToDate,103) as ToDate,
ROW_NUMBER() OVER(PARTITION BY C.ChargeCode Order by Convert(varchar,CM.ToDate,103)desc)as ID From QMS_Tbl_CustomClearanceTariffMaster CM
INNER JOIN QMS_Tbl_CustomClearanceTariffDetail CD ON CM.CustomClearanceTariffID = CD.CustomClearanceTariffID
INNER JOIN ERP_Tbl_ChargeCode C ON CD.ChargeCodeID = C.ChargeCodeId
INNER JOIN ERP_Tbl_ChargeCodeRelation CR ON C.ChargeCodeId = CR.ChargeCodeId
INNER JOIN ERP_Tbl_ChargeCodeSet CS ON CR.ChargeCodeSetId = CS.ChargeCodeSetId

WHERE CD.Mode = @Mode and CD.PortType = @PortType and ((CM.FromDate BETWEEN convert(DateTime, @FromDate ,103)
AND convert(DateTime, @ToDate ,103))
OR (CM.ToDate BETWEEN convert(DateTime, @FromDate ,103)
AND convert(DateTime, @ToDate ,103)) OR (convert(DateTime, @FromDate ,103) BETWEEN CM.FromDate AND CM.ToDate)
OR (convert(DateTime, @ToDate ,103) BETWEEN CM.FromDate
AND CM.ToDate)))Z
Where Z.ID = 1 [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources