Announcement
Collapse
No announcement yet.
Need help with grouping and displaying the last record only with the total value.
Collapse
X
-
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.
-
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
- Likes 1
Leave a comment:
-
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
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
Tags: None
Leave a comment: