Hi,
For better enhancement and to avoid manual modification every month, i need to modify my current query such a way that every month when i run this query in the Oracle SQL, system will run the query for Last month end date of the based upon current system date.
What I have tried:
SELECT ACCOUNT.ACCOUNT_NAME, SECURITY.SECURITY_FORMATTED_NAME, SECURITY.SECURITY_NAME, SECURITY.SECURITY_CATG_CODE, ACCOUNT_POSITION.ACCOUNT_MARKET_VALUE, ACCOUNT_POSITION.SHARES, COUNTRY.COUNTRY_DISCRIPTION, INDUSTRY_DIM.INDUSTRY_GROUP_NAME, SECURITY.SECURITY_CUSIP, SECURITY.SECURITY_SEDOL, SECURITY.SECURITY_ISIN, SECURITY.SECURITY_INTERNAL_ID, LAST_DAYE(SYSDATE(DAY_DIM.DAY_DIM_ID))
FROM ACCOUNT, ACCOUNT_POSITION, COUNTRY, DAY_DIM, INDUSTRY_DIM, SECURITY, POSITION_TYPE
WHERE ACCOUNT_POSITION.ACCOUNT_ID = ACCOUNT.ACCOUNT_ID
AND ACCOUNT_POSITION.POSITION_TYPE_ID = POSITION_TYPE.POSITION_TYPE_ID
AND ACCOUNT_POSITION.SECURITY_ID = SECURITY.SECURITY_ID
AND ACCOUNT_POSITION.DAY_ID = SECURITY.DAY_ID
AND ACCOUNT_POSITION.DAY_ID = DAY_DIM.DAY_ID
AND COUNTRY.COUNTRY_CODE = SECURITY.SECURITY_COUNTRY_CODE
AND SECURITY.INDUSTRY_ID = INDUSTRY_DIM.INDUSTRY_ID
AND ((ACCOUNT.ACCOUNT_NUMBER = ‘12345’)
AND (POSITION_TYPE.POSITION_TYPE_ID_CODE = ‘Trade’))