查找价格发生更改时每个项目的最新生效日期 - SQL Server 2014

Question

>我是SQL Server 2014的新人。我有一个表格，上面有如下记录。

Year | ItemName | price     | effectivefromdate
===============================================  
2018 | item27   | 1595.0000 | 2017-01-01       
2018 | item27   | 1595.0000 | 2018-03-01       
2018 | item29   | 1000.0000 | 2017-01-01       
2018 | item29   | 1100.0000 | 2018-03-01       
2018 | item30   | 1795.0000 | 2017-01-01       
2018 | item30   | 1795.0000 | 2018-03-01 
2018 | item30   | 1795.0000 | 2018-06-01 
2018 | item32   | 1322.0000 | 2017-01-01       
2018 | item32   | 1350.0000 | 2018-03-01 
2018 | item32   | 1376.0000 | 2018-06-01 

在这里，每个项目都有一行或多行相同或不同的价格。当价格发生变化时，我必须为每个项目采取最新的生效日期 如果有多个生效日期没有价格变化，那么我必须退回具有最小生效日期的商品。

例如，item27 有两个条目，但价格没有改变，所以我必须将价格定为 1595，生效日期为 2017-01-01 如果是item29，这里的价格发生了变化，我必须以1100作为价格，生效日期为2018-03-01。

Expected Output
Year | ItemName | price     | effectivefromdate
===============================================  
2018 | item27   | 1595.0000 | 2017-01-01          
2018 | item29   | 1100.0000 | 2018-03-01       
2018 | item30   | 1795.0000 | 2017-01-01      
2018 | item32   | 1376.0000 | 2018-06-01  

我尝试使用滞后/领先功能，但没有运气。过去两天我一直在为此苦苦挣扎。

请向我建议一些解决方案来解决此问题。

Answer 1

通过使用Row_Number()：

with cte as
(
Select Year, Itemname,price,effectivefromdate, 
ROW_NUMBER() over (Partition by ItemName order by price desc, effectivefromdate asc) as ranking
from tbl 
)
Select  Year, Itemname,price,effectivefromdate from cte where ranking = 1

注意：这仅在价格随时间上涨时才有效。

Answer 2

您似乎想要最新价格的生效日期。

这个想法是获取具有最终价格的行集 - 或者换句话说，这些行没有具有较大时间戳的不同价格。

然后聚合以获得最早的生效日期：

select year, itemname, price, min(effectivefromdate)
from t
where not exists (select 1
from t t2
where t2.year = t.year and
t2.itemname = t.itemname and
t2.effectivefromdate > t.effectivefromdate and
t2.price <> t.price
)
group by year, itemname, price;

您也可以将其视为差距和孤岛问题。 然而，这可能很棘手 - 特别是如果价格可以在两者之间重复变化。

Answer 3

您也可以使用row_number和分组，如下所示。在此处查看工作演示

; with cte as
(
select *, r= row_number() over( partition by ItemName  order by effectivefromdate desc) from t
)
select 
t1.Year,
t1.ItemName,
t1.Price,
effectivefromdate=min(t2.effectivefromdate) from cte t1 join
t t2 on r=1 and t1.Year=t2.Year
and t1.ItemName=t2.ItemName and t1.price=t2.price
group by 
t1.Year,t1.ItemName,t1.Price