如何使用 Max(date) 获取记录，然后比较值以获得结果 [英] How do I get record with Max(date) and then compare values to get result
问题描述
我正在尝试使用 Max(StartDate) 获取每个 MeterNumber 的记录，然后尝试获取 startdate、enddate & 的记录.特定帐号的 RateCode 不同.
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.
示例数据脚本如下
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***
首先，我需要为特定的 AccountNumber 找到每个 MeterNumber 的 Max(StartDate).输出应该是这样的:
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***
任何帮助将不胜感激！
推荐答案
这是一个 3 步过程，首先使用 ROW_NUMBER() 对每个 Account/Meter 组合的记录进行排名
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
输出
| 帐号 | MeterNumber | 开始日期 | EndDate | RateCode | RowNumber |
|---|---|---|---|---|---|
| 0142628117 | 123470203 | 2020-04-22 | 9999-12-31 | ETF0_APR20 | 1 |
| 0142628117 | 123470203 | 2019-04-10 | 2020-04-09 | ***自定义*** | 2 |
| 0142628117 | 123470205 | 2020-04-22 | 9999-12-31 | ETF0_APR20 | 1 |
| 0142628117 | 123470205 | 2019-04-10 | 2020-04-09 | ***自定义*** | 2 |
| 1363445 | 105238304 | 2018-10-02 | 2019-08-11 | ***自定义*** | 1 |
| 1363445 | 105238304 | 2016-02-25 | 2016-04-22 | ***自定义*** | 2 |
| 1363445 | 130359929 | 2019-08-12 | 9999-12-31 | ***自定义*** | 1 |
注意一些数据被缩短以更好地显示
然后您可以过滤 was RowNumber = 1 以获得每个仪表的最新结束日期.
Then you can filter for were RowNumber = 1 to get the latest end date for each meter.
接下来您需要计算不同的 EndDate/RateCode 组合，您不能在窗口函数中使用 COUNT(DISTINCT ...)，但是您可以使用 DENSE_RANK():
Next you need to count the distinct EndDate/RateCode combinations, you can't use COUNT(DISTINCT ...) in a windowed function, however you can emulate this using 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;
输出
| 帐号 | MeterNumber | 开始日期 | EndDate | RateCode | CntDistinct |
|---|---|---|---|---|---|
| 0142628117 | 123470203 | 2020-04-22 | 9999-12-31 | ETF0_APR20 | 1 |
| 0142628117 | 123470205 | 2020-04-22 | 9999-12-31 | ETF0_APR20 | 1 |
| 1363445 | 130359929 | 2019-08-12 | 9999-12-31 | ***自定义*** | 2 |
| 1363445 | 105238304 | 2018-10-02 | 2019-08-11 | ***自定义*** | 2 |
注意一些数据被缩短以更好地显示
最后将所有这些放到一个进一步的子查询中，并限制在有多个 EndDate/RateCode 的唯一组合的地方:
Finally put all this into a further subquery and limit to where there is more than one unique combination of 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;
输出
| 帐号 | MeterNumber | 开始日期 | EndDate | RateCode |
|---|---|---|---|---|
| 0500000178767001363445 | TCA105238304 | 2018-10-02 | 2019-08-11 | ***自定义*** |
| 0500000178767001363445 | TCA130359929 | 2019-08-12 | 9999-12-31 | ***自定义*** |
这篇关于如何使用 Max(date) 获取记录，然后比较值以获得结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
