我如何獲得具有Max(date)的記錄，然后比較數值以獲得結果？ -有解無憂

我試圖為每個電表號碼獲取具有Max(StartDate)的記錄，然后試圖獲取起始日期、結束日期和amp;RateCode對于特定AccountNumber不同的記錄。

示例資料腳本如下

create table Meter 
(
    AccountNumer varchar（50）。
    MeterNumber varchar（50）。
    StartDate date, 
    EndDate date, 
    RateCode Varchar(50)
)

插入 進入Meter Values('0142628117', '123470203','4/22/2020','12/31/9999','UGE-PECO-E-V-GRN-HOLDOVER-ETF0_APR20')
插入 進入米值('0142628117', '123470203','4/10/2019', '4/9/2020', '***Custom**')
插入 進入米值('0142628117', '123470205','4/22/2020','12/31/9999','UGE-PECO-E-V-GRN-HOLDOVER-ETF0_APR20')
插入 進入米值('0142628117', '123470205','4/10/2019', '4/9/2020', '***Custom**')
    
插入 進入米值('0500000178767001363445'。 'TCA105238304','02/25/2016','04/22/2016', '***Custom**')
插入 進入米值('0500000178767001363445'。 'TCA105238304','10/2/2018','08/11/2019', '***Custom**')
插入 進入米值('0500000178767001363445'。 'TCA130359929','8/12/2019','12/31/9999', '***Custom**')

RowNo AccountNumer MeterNumber StartDate EndDate RateCode
    1 0142628117 123470203 2020-04-22 9999-1231 UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20
    2 0142628117 123470203 2019-04-10 2020-04-09 ***Custom***
    3 0142628117 123470205 20 -04-22 9999-1231 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***

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

 RowNo AccountNumer MeterNumber StartDate EndDate RateCode
        1 0142628117 123470203 2020-04-22 9999-1231 UGE-PECO-E-R-V-GRN-HOLDOVER-ETF0_APR20
        3 0142628117 123470205 2020 -04-22 9999-1231 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()對每個賬戶/表的組合進行記錄排序

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

OUTPUT

StartDate結束日期RateCodeRowNumber(行號)。

AccountNumber	MeterNumber
0142628117	123470203?	2020-04-22	2020-04-22>	9999-12-31	9999-12-31?	ETF0_APR20	ETF0_APR20?	1
0142628117?	123470203?	2019-04-10	2020-04-09	2020-04-09?	2
0142628117?	123470205?	2020-04-22	2020-04-22>	9999-12-31	9999-12-31?	ETF0_APR20	ETF0_APR20?	1
0142628117?	123470205?	2019-04-10	2020-04-09	2020-04-09?	2
1363445?	105238304	105238304?	2018-10-02	2018-10-02>	2019-08-11		1
1363445?	105238304	105238304?	2016-02-25	2016年2月25日	2016-04-22	2016-04-22>	2
1363445?	130359929	130359929	2019-08-12		9999-12-31	1

_{N.B.一些資料被縮短以顯示得更好}

然后你可以過濾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;

OUTPUT

StartDate結束日期RateCodeCntDistinct

AccountNumber	MeterNumber
0142628117	123470203?	2020-04-22	2020-04-22>	9999-12-31	9999-12-31?	ETF0_APR20	ETF0_APR20?	1
0142628117?	123470205?	2020-04-22	2020-04-22>	9999-12-31	9999-12-31?	ETF0_APR20	ETF0_APR20?	1
1363445?	130359929	130359929	2019-08-12		9999-12-31	2
1363445?	105238304	105238304?	2018-10-02	2018-10-02>	2019-08-11		2

_{N.B.一些資料被縮短了，以便更好地顯示}

最后將所有這些放入一個進一步的子查詢中，并限制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;

OUTPUT

StartDate結束日期RateCode

AccountNumber	MeterNumber
0500000178767001363445	TCA105238304?	2018-10-02	2019-08-11
0500000178767001363445?	TCA130359929	TCA130359929	2019-08-12	9999-12-31

db<>fiddle上的例子

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/314803.html

標籤：

上一篇：關于連接條件的問題。需要比較從A點到B點的運輸數量

下一篇：根據開始和結束日期，將日期分成季度