Click here to Skip to main content
15,884,605 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

SQL
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’))
Posted
Updated 29-Mar-23 1:36am
v2

1 solution

See here: SQL Server DATEADD() Function[^] - if you provide it with negative numbers, it goes backwards. So subtract today's day-of-month from the current date and you get the last day of last month ...
 
Share this answer
 
Comments
jsc42 29-Mar-23 6:34am    
OP says Oracle, not SQL SERVER; Oracle does not have a DATEADD function. https://stackoverflow.com/questions/4957224/getting-last-day-of-previous-month-in-oracle-function says use LAST_DAY(ADD_MONTHS(sysdate, -1))

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900