select diferent values diferent rows

I have a table cad_changes

     id          unit_id       old_value        new_value         change_date

    4615128         800          88809433        83028302     2018-03-01 17:02:59
    4615144         800          83028302        87434702     2018-03-13 12:21:24
    4615145         800          87434702        87869589     2018-03-13 12:27:56
    4615150         800          87869589        100148757    2018-03-14 23:20:07

when I can us select that return this result

when change_date = 2018-03-01 then 83028302 - 88809433
when change_date = 2018-03-13 then 87869589 - 83028302
when change_date = 2018-03-01 then 100148757 - 87869589

I want to result select max (new_value) - min(old_value) where date = trunc(change_date)

1 answer

  • answered 2018-03-14 20:22 Barbaros Özhan

    This sql should be enough :

    select trunc(change_date) change_date, max (new_value)||' - '||min(old_value) values
      from cad_changes
     group by trunc(change_date)
     order by trunc(change_date);