SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Meter (AccountNumber varchar(50), MeterNumber varchar(50), StartDate date, EndDate date, RateCode Varchar(50))
Insert into Meter Values('0142628117','123470203','4/22/2020','12/31/9999','UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20')
Insert into Meter Values('0142628117','123470203','4/10/2019', '4/9/2020', '***Custom***')
Insert into Meter Values('0142628117','123470205','4/22/2020','12/31/9999','UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20')
Insert into Meter Values('0142628117','123470205','4/10/2019', '4/9/2020', '***Custom***')
    
Insert into Meter Values('0500000178767001363445','TCA105238304','02/25/2016','04/22/2016', '***Custom***')
Insert into Meter Values('0500000178767001363445','TCA105238304','10/2/2018','08/11/2019', '***Custom***')
Insert into Meter Values('0500000178767001363445','TCA130359929','8/12/2019','12/31/9999', '***Custom***')
7 rows affected

SELECT *,
      RowNumber = ROW_NUMBER() OVER(PARTITION BY AccountNumber, MeterNumber ORDER BY EndDate DESC)
FROM Meter


AccountNumber
MeterNumber
StartDate
EndDate
RateCode
RowNumber


0142628117
123470203
2020-04-22
9999-12-31
UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20
1

0142628117
123470203
2019-04-10
2020-04-09
***Custom***
2

0142628117
123470205
2020-04-22
9999-12-31
UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20
1

0142628117
123470205
2019-04-10
2020-04-09
***Custom***
2

0500000178767001363445
TCA105238304
2018-10-02
2019-08-11
***Custom***
1

0500000178767001363445
TCA105238304
2016-02-25
2016-04-22
***Custom***
2

0500000178767001363445
TCA130359929
2019-08-12
9999-12-31
***Custom***
1


SELECT *,
		EndDateRateCount = DENSE_RANK() OVER(PARTITION BY AccountNumber ORDER BY EndDate, RateCode) 
						+ DENSE_RANK() OVER(PARTITION BY AccountNumber ORDER BY EndDate DESC, RateCode DESC) - 1
FROM (	SELECT *,
			  RowNumber = ROW_NUMBER() OVER(PARTITION BY AccountNumber, MeterNumber ORDER BY EndDate DESC)
		FROM Meter AS m
	) AS m
WHERE m.RowNumber = 1


AccountNumber
MeterNumber
StartDate
EndDate
RateCode
RowNumber
EndDateRateCount


0142628117
123470203
2020-04-22
9999-12-31
UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20
1
1

0142628117
123470205
2020-04-22
9999-12-31
UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20
1
1

0500000178767001363445
TCA130359929
2019-08-12
9999-12-31
***Custom***
1
2

0500000178767001363445
TCA105238304
2018-10-02
2019-08-11
***Custom***
1
2


SELECT AccountNumber, MeterNumber, StartDate, EndDate, RateCode
FROM (	SELECT *,
				EndDateRateCount = DENSE_RANK() OVER(PARTITION BY AccountNumber 
													ORDER BY EndDate, RateCode) 
						+ DENSE_RANK() OVER(PARTITION BY AccountNumber 
											ORDER BY EndDate DESC, RateCode DESC) - 1
			FROM (	SELECT *,
						  RowNumber = ROW_NUMBER() OVER(PARTITION BY AccountNumber, MeterNumber 
													ORDER BY EndDate DESC)
					FROM Meter AS m
				) AS m
			WHERE m.RowNumber = 1
	) AS m
WHERE m.EndDateRateCount > 1;


AccountNumber
MeterNumber
StartDate
EndDate
RateCode


0500000178767001363445
TCA130359929
2019-08-12
9999-12-31
***Custom***

0500000178767001363445
TCA105238304
2018-10-02
2019-08-11
***Custom***

AccountNumber	MeterNumber	StartDate	EndDate	RateCode	RowNumber
0142628117	123470203	2020-04-22	9999-12-31	UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20	1
0142628117	123470203	2019-04-10	2020-04-09	*Custom*	2
0142628117	123470205	2020-04-22	9999-12-31	UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20	1
0142628117	123470205	2019-04-10	2020-04-09	*Custom*	2
0500000178767001363445	TCA105238304	2018-10-02	2019-08-11	*Custom*	1
0500000178767001363445	TCA105238304	2016-02-25	2016-04-22	*Custom*	2
0500000178767001363445	TCA130359929	2019-08-12	9999-12-31	*Custom*	1