transforming data

Database
Enthusiast

transforming data

I have data in below format 

product  seqnum DATE
a 1 1987-06-24
a 2 1987-06-24
a 3 2014-11-20
a 4 2015-03-25

and i am looking to convert into this

product seqnum startdate ENDDATE
a 1 1987-06-24 1987-06-25
a 2 1987-06-25 2014-11-20
a 3 2014-11-20 2015-03-25
a 4 2015-03-25 3499-12-31

Basically the max sequnce no would have the endtime of max. 

SELECT product,SequenceNum,startDate,
ROW_NUMBER() OVER (PARTITION BY product ORDER BY SequenceNum ) maxrow
CASE WHEN startdate=enddate THEN DATE '3499-12-31' ELSE end_Date END
FROM (
SELECT
product ,SequenceNum,startDate,
MAX(startDate) OVER (PARTITION BY productORDER BY SequenceNum
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ) AS enddate
,
MAX(startDate) OVER (PARTITION BY product ) AS Max_End_Dt_Tm

FROM table
WHERE product=1
)
a
1 REPLY
Enthusiast

Re: transforming data

SELECT PRODUCT,SEQNUM,STARTDATE,COALESCE(MAX(STARTDATE) OVER(PARTITION BY PRODUCT ORDER BY SEQNUM 1 FOLLOWING 1 FOLLOWING) ,'9999-12-31' /*HIGH_END_DT VALUE*/) FROM TABLE