CODEWITHSUNDEEP
sql-server-2012ranking
Emraan
Emraan

Reputation: 143

Update the Rank 2 values based on Rank1 value column data

Data present in the table is as below:

Query: select id, actv_ind, valid_from, valid_to, rnk from #ABC

enter image description here

Expected Data is as below: I want to have the Valid_from date of Rank 2 records to be set as (Valid_from date of Rnk 1 record - 1)

Query tried:

select t1.id, t1.actv_ind, t1.rnk, case when t1.rnk >1 then t1.valid_from end VF1, case when t2.rnk > 1 then t1.valid_from - 1 end VF2
from #ABC t1
inner join #ABC t2
on t1.id = t2.id
and t1.actv_ind = t2.actv_ind

enter image description here

Upvotes: 0

Views: 219

Answers (1)

Thom A
Thom A

Reputation: 95561

Just use LAG:

SELECT mtrl_id,
       actv_ind,
       rnk
       LAG(DATEADD(DAY, -1, valid_from),1,valid_from) OVER (PARTITION BY mtrl_id ORDER BY Rank) AS valid_from,
       valid_to
FROM dbo.YourTable;

Upvotes: 1

Related Questions