0

Good evening everyone,

I am looking for the most elegant way to substract two values of one column based on the value in another column. Until now I could only find hacky and unreadable solutions:

I have a table containing entries when orders on an exchange were opened and closed. It looks like this:

|| DATETIME || ORDERID || ACTION || TYPE || PRICE || ...
  2018-01-01   a34hsbf    OPEN      LONG    0.23
  2018-01-02   ghs7fa0    OPEN      SHORT   0.45
  2018-01-03   a34hsbf    CLOSE     LONG    0.47
  ...

Now I want to return a new table containing the differences between entries with the same ORDERID. There can always only be two entries with the same ORDERID. The new table should be indexed by the greater DATETIME of those two entries. Also the difference should take into account if the TYPE was LONG or SHORT. E.g. for LONG a rising price must give a positive difference, for SHORT it must give a negative difference and vice versa.

The new table should look like the following:

|| DATETIME || ORDERID || TYPE || DIFF ||
  2018-01-03   a34hsbf    LONG    0.24
  ...

Many thanks in advance for your help. :)

  • Do you have a working "hacky" or "unreadable" solution? If so, include that. What's "elegant" to one person could be hacky or unreadable to another. Also, could you ever have an "open" listed as a "long" and a "close" listed as a "short", or vice versa? Logically you shouldn't, but I don't know if your DB/app enforces that logic in some way. – RDFozz Mar 8 '18 at 19:25
  • No, unfortunately I haven't saved them since none was to my liking. But you are right, beauty is a subjective topic. However, McNets solution below is exactly what I was looking for. – Nimrare Mar 8 '18 at 22:51
1

As RDFozz has pointed out in his comments, this solution only works if:

  • There is one OPEN and one CLOSE entries
  • Both entries has the same type, 'LONG' or 'SHORT'

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

dbfiddle here

| improve this answer | |
  • Thank you McNets, that is really beautiful, exactly what I was looking for. Unfortunately, I can't give you an upvote due to my reputation but that is the best solution I have seen so far. ;) – Nimrare Mar 8 '18 at 22:50
  • You should be able to accept his answer, in any case, on your own question. – RDFozz Mar 8 '18 at 22:52
  • Ah, yes, you are right, did that. :) – Nimrare Mar 8 '18 at 23:12
  • 1
    You could add AND t1.type = t2.type AND t1.type IN ('SHORT', 'LONG'), either in ON or WHERE condition, to avoid returning results from possible bogus data. – ypercubeᵀᴹ Mar 9 '18 at 9:24
  • @ypercubeᵀᴹ maybe it could be better add: t1.type = t2.type in ON condition? – McNets Mar 9 '18 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.