CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-server-2008-r2sql-server-2014
StackUser
StackUser

Reputation: 5398

Find latest effective date for each item when there is a price change - SQL Server 2014

I am new the SQL Server 2014. I have a table with records like the below. 

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

Here each item have one or more lines with same or different prices. I have to take the latest effective date for each item when the price has changed other wise If there is no price change with multiple effective dates then I have to return the item with minimum effective date.

For example, item27 have two entries but the price is not changed so I have to take price as 1595 and effective date as 2017-01-01 In case of item29, the price has changed here I have to take 1100 as price and effective date as 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

I tried with Lag/Lead function but no luck. I am struggling for the past two days with this.

Please suggest me some solution to solve this.

Upvotes: 2

Views: 836

Answers (3)

Ajay Gupta
Ajay Gupta

Reputation: 1855

By using 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

Note: This works only when price increases with time.

Upvotes: 3

DhruvJoshi
DhruvJoshi

Reputation: 17136

You can also using row_number and with group by like below. See a working demo here

; 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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You seem to want the effective date of the most recent price.

The idea is to get the set of rows that have the final price -- or said differently, that do not have a different price with a larger timestamp.

Then aggregate to get the earliest effective date:

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;

You can also approach this as a gaps-and-islands problem. However, this can be tricky -- particularly if prices can repeat with changes in-between.

Upvotes: 2

Related Questions