0

I have different trades that I want to group by Entry date and Symbol.

This is the Origin Data: (these are all values I get from the User)

Date        Time       Symbol     Ordertype    Price     Shares (Note for you)
01/01/20    09:30:00   ABC        B            $5.00     500    (Day Trade)
01/01/20    10:30:00   ABC        S            $7.00     500    (Day Trade)
02/01/20    15:00:00   XYZ        B            $10.00    250    (Multi Day Trade)
04/01/20    10:00:00   XYZ        S            $20.00    250    (Multi Day Trade)
...

This is the Goal: (Entry Date = first order day)

EntryDate   Symbol     Profit     TotalShares   Type
01/01/20    ABC        1000.00    1000          Day Trade
02/01/20    XYZ        2500.00    500           Multi Day Trade

As I also have MULTI-DAY Trades (where the order date is different) I can not group by "Date";

I thought about adding a calculated column like "remaining Position" or something in this direction ...

As I am relatively new to advanced SQL I hope someone can share his thoughts on this ...

1 Answers1

1

You can use aggregation:

select EntryDate, Symbol,
       sum(case when ordertype = 'B' then - shares * price
                when ordertype = 'S' then shares * price
                else 0
           end) as Profit,
       TotalShares, Type
from t
group by EntryDate, Symbol, TotalShares, Type
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, but it seams as if I did not wrote my case clearly - I used "()" in my origin data table to indicate it as a note for your reference => it's not a real value I have in my table ;)
    Also do I only have orderdate in the origin table but the goal is to find the date of the first order of the trade and display it as "EntryDate"     –  Nov 17 '20 at 12:02
  • @Dorbin4 . . . This answers the question that you have asked here. If you intended to ask a different question, then ask a *new* question with the sample data that really expresses what you have and an explanation of the logic that you really want to implement. – Gordon Linoff Nov 17 '20 at 13:56