如何使用Max(date)獲取記錄，然后比較值以獲得結果

我正在嘗試獲取每個 MeterNumber 的 Max(StartDate) 記錄，然后嘗試獲取特定 AccountNumber 的 startdate、enddate 和 RateCode 不同的記錄。

示例資料腳本如下

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***

首先，我需要Max(StartDate)為每個MeterNumber特定的AccountNumber. 輸出應該是這樣的：

    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***

然后我試圖從（兩行）獲取帳號的開始日期、結束日期和費率代碼不同的記錄。所以我期望的最終結果如下表所示。

   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***

任何幫助將不勝感激！

uj5u.com熱心網友回復：

這是一個 3 步程序，首先使用 ROW_NUMBER() 對每個 Account/Meter 組合的記錄進行排名

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

輸出

_{NB 一些資料被縮短以更好地顯示}

然后，您可以過濾 RowNumber = 1 以獲取每個儀表的最新結束日期。

接下來，您需要計算不同的 EndDate/RateCode 組合，您不能COUNT(DISTINCT ...) 在視窗函式中使用，但是您可以使用以下方法進行模擬DENSE_RANK()：

SELECT *,
        CntDistinct = 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;

輸出

_{NB 一些資料被縮短以更好地顯示}

最后將所有這些放入一個進一步的子查詢中，并將 EndDate/RateCode 的唯一組合限制為多個：

SELECT AccountNumber, MeterNumber, StartDate, EndDate, RateCode
FROM (  SELECT *,
                CntDistinct = 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.CntDistinct > 1;

輸出

db<>Fiddle 上的示例

標籤：

上一篇：對GroupByCondition下的結果求和

下一篇：如何比較來自同一列和不同行但同一表的值？