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 :
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:
I am using DB2 to the sql statement.
I appreciate any help on this topic
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 |
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 appreciate any help on this topic
Comment