clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1228830 fiddles created (16658 in the last week).

create table tbl (DATETIME date, ORDERID text, ACTION text, TYPE text, PRICE numeric (10,2)); insert into tbl values ('2018-01-01', 'a34hsbf', 'OPEN', 'LONG', 0.23), ('2018-01-02', 'ghs7fa0', 'OPEN', 'SHORT', 0.45), ('2018-01-03', 'a34hsbf', 'CLOSE', 'LONG', 0.47), ('2018-01-03', 'ghs7fa0', 'CLOSE', 'LONG', 0.48);
4 rows affected
 hidden batch(es)


select case when t1.datetime > t2.datetime then t1.datetime else t2.datetime end as datetime, t1.orderid, t1.type, case when t1.type = 'SHORT' then t1.price - t2.price else t2.price - t1.price end as diff from tbl t1 join tbl t2 on t1.orderid = t2.orderid and t1.type = t2.type and t2.action = 'CLOSE' where t1.action = 'OPEN'
datetime orderid type diff
2018-01-03 a34hsbf LONG 0.24
 hidden batch(es)