I have a column(EFFECTIVE_START_DATE) which has to be transformed to INFOMATION_DATE as the last date of the month.
ex : if my source dates are 26th jan 2014, 26th feb 2014 and 26 march 2014, I have to create a view which will contain data in the following way :
for jan-> 1 record
for feb-> 2 records(jan and feb)
for march-> 3 records(jan feb and march)
expected result set:
Can some one help me acheive the above result set.
Thanks in advance
You can achieve the functionalty by finding last day of the previous month and then add 1 month to it
SELECT add_months((current_Date - EXTRACT(day from current_Date)),1);
SELECT add_months((cast('2014-02-26' AS DATE)- EXTRACT(day from cast('2014-02-26' AS DATE))),1);
select t1.col1 as effective_dt,last_day(t2.col1) as information_dt from test t1,test t2
order by t2.col1,t1.col1
Table test contains following data
Result of the query is as follows,
Kindly let me know if this does not meet your requirement.