CODEWITHSUNDEEP
sql
Roadside Romeozz
Roadside Romeozz

Reputation: 83

Select Previous row date as current row date

I have a Table like below enter image description here I need it as below enter image description here

Group by CardNo and when insured id is different BirthDate should be previous row End_Date Thanks in advance

Upvotes: 0

Views: 56

Answers (3)

GMB
GMB

Reputation: 222402

You can use lag(). It is not very clear which column(s) can be used to order the records, but the idea is:

select card_no, insured_no, 
    lag(end_date, 1, birth_date) over(partition by card_no order by insured_no) birth_date,
    end_date
from mytable

Ideally, you would replace insured_no in the order by clause with a column (or a set of columns) that uniquely identifies the position of each row.

Upvotes: 0

Poonam Kumari
Poonam Kumari

Reputation: 199

Here is the answer

select card_no, 
    insured_no, 
    lag(end_date, 1, birth_date) over (partition by card_no order by end_date, birth_date) as birth_date,
    end_date
from table_name

Have to pass (end_date, 1, birth_date) in lag, otherwise, birth_date for the first row will not be correct as you need the same birth_date for the first row.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

This is tricky because of the duplicates. I would start with lag():

select t.*,
       lag(end_date) over (partition by card_no order by end_date, birth_date) as col
from t;

However, the results are unstable and would be better if there were a unique id on each row.

Upvotes: 0

Related Questions