sql server - How do I get record with Max(date) and then compare values to get result

Question

I am trying to get record with Max(StartDate) for each MeterNumber and then try to get records where startdate, enddate & RateCode is different for specific AccountNumber.

Sample data script as below

create table Meter 
(
    AccountNumer 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***')

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

First I need to find Max(StartDate) for each MeterNumber for specific AccountNumber. Output should be like this:

    RowNo   AccountNumer            MeterNumber        StartDate   EndDate     RateCode
        1   0142628117              123470203       2020-04-22  9999-12-31  UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20
        3   0142628117              123470205       2020-04-22  9999-12-31  UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20
        6   0500000178767001363445  TCA105238304    2018-10-02  2019-08-11  ***Custom***
        7   0500000178767001363445  TCA130359929    2019-08-12  9999-12-31  ***Custom***

And then I am trying to get just records where start date, end date and ratecode are different for account number from the (both rows). so the final result I am expecting as below for above table.

   RowNo   AccountNumer            MeterNumber        StartDate   EndDate     RateCode
       6    0500000178767001363445  TCA105238304    2018-10-02  2019-08-11  ***Custom***
       7    0500000178767001363445  TCA130359929    2019-08-12  9999-12-31  ***Custom***

Any help will be really appreciated !

Answer 1

This is a 3 step process, first rank your records for each Account/Meter combination using ROW_NUMBER()

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

OUTPUT

AccountNumber	MeterNumber	StartDate	EndDate	RateCode	RowNumber
0142628117	123470203	2020-04-22	9999-12-31	ETF0_APR20	1
0142628117	123470203	2019-04-10	2020-04-09	***Custom***	2
0142628117	123470205	2020-04-22	9999-12-31	ETF0_APR20	1
0142628117	123470205	2019-04-10	2020-04-09	***Custom***	2
1363445	105238304	2018-10-02	2019-08-11	***Custom***	1
1363445	105238304	2016-02-25	2016-04-22	***Custom***	2
1363445	130359929	2019-08-12	9999-12-31	***Custom***	1