ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Need help with grouping and displaying the last record only with the total value.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need help with grouping and displaying the last record only with the total value.

    I have the following query :

    SELECT TRADE_DATE ,VALUE_DATE ,PORTFOLIO_CODE PORTFOLIO_NAME, MATURITY_DATE, COPOUN_RATE, HOLDINGS, TRANSACTION_TYPE ,CURRENCY, CUSTODIAN_CODE, CUSTODIAN_NAME from TABLE_XYZ

    And the result shows as :
    TRADE_DATE VALUE_DATE PORTFOLIO_CODE PORTFOLIO_NAME MATURITY_DATE COPOUN_RATE HOLDINGS TRANSACTION_TYPE CURRENCY CUSTODIAN_CODE CUSTODIAN_NAME
    20180709 20180711 009001 ABC 20301001 1.5 10000000 BUY USD 123 OLD COMPANY NAME
    20181231 20181231 009001 ABC 20301001 1.5 10000000 BUY USD 123 OLD COMPANY NAME
    20181231 20181231 009001 ABC 20301001 1.5 -10000000 SELL USD 123 OLD COMPANY NAME
    20190102 20190104 009001 ABC 20301001 1.5 -10000000 SELL USD 123 OLD COMPANY NAME
    20190617 20190619 009001 ABC 20301001 1.5 5000000 BUY USD 123 OLD COMPANY NAME
    20190701 20190703 009001 ABC 20301001 1.5 5000000 BUY USD 123 OLD COMPANY NAME
    20190827 20190829 009001 ABC 20301001 1.5 5000000 BUY USD 123 OLD COMPANY NAME
    20191118 20191120 009001 ABC 20301001 1.5 5000000 BUY USD 456 NEW COMPANY NAME
    20191231 20191231 009001 ABC 20301001 1.5 -20000000 SELL USD 456 NEW COMPANY NAME
    20191231 20191231 009001 ABC 20301001 1.5 20000000 BUY USD 456 NEW COMPANY NAME
    Is it possible to get the result to show the sum of holdings with the last record dated in the trad_date as of 20191231 ?
    for example to have the result looks like this:
    TRADE_DATE VALUE_DATE PORTFOLIO_CODE PORTFOLIO_NAME MATURITY_DATE COPOUN_RATE HOLDINGS TRANSACTION_TYPE CURRENCY CUSTODIAN_CODE CUSTODIAN_NAME
    20191231 20191231 009001 ABC 20301001 1.5 2000000 Column not required USD 456 NEW COMPANY NAME
    I am using DB2 to the sql statement.

    I appreciate any help on this topic

  • #2
    If I have understood correctly, then this should do it:
    Code:
    select TRADE_DATE, VALUE_DATE, PORTFOLIO_CODE, PORTFOLIO_NAME, MATURITY_DATE,
           COPOUN_RATE, SUM_HOLDINGS, CURRENCY, CUSTODIAN_CODE,
           CUSTODIAN_NAME
      from TABLE_XYZ
     cross join table (select sum(HOLDINGS) as SUM_HOLDINGS
                         from TABLE_XYZ) sh
     order by TRADE_DATE desc
     limit 1

    Comment


    • #3
      Thanks Vectorspace for the idea, but I used inner join with group by and converted the trade date to date and then used the MAX and joined the PORTFOLIO CODE, and then I sum up the holdings in the inner join.
      Last edited by Matt_99999; January 22, 2020, 11:12 PM.

      Comment

      Working...
      X