oracle12c - Adding days to the select query result dates in oracle

Question

select from_date_su_rela,to_date_su_rela from RELATION_T;

But the expectation is reducing -56 days from the first row from_date_su_rela and adding the +56 days to last(3rd) row to_date_su_rela. as below

I have written query as ,

select from_date_su_rela-56,to_date_su_rela+56 from RELATION_T; But its adding and reducing days from all the rows as below,

How to make it working as above 2nd image.

Answer 1

One option is to use the row_number analytic function sorting the data both ascending and descending to find the first and last row and then perform the addition and subtraction in a case statement

select case when rn_asc = 1
            then from_date_su_rela - 56
            else from_date_su_rela
         end from_date_su_rela,
       case when rn_desc = 1
            then to_date_su_rela + 56
            else to_date_su_rela
         end to_date_su_rela
  from (
      select from_date_su_rela,
             to_date_su_rela,
             row_number() over (order by from_date_su_rela desc) rn_desc,
             row_number() over (order by from_date_su_rela asc) rn_asc
        from relation_t
  )