Question

date functions using Oracle

Oracle, Oracle SQL, SQL, Oracle development

Hi,

I need to display the benefits paid for the current year using SQl,but the year is filtered using the year which is previous year for the current year.the date is in 'yyyymm' format.
ex,
select year_mnth,benefits_paid
from tablename
where year_mnth in ('200603','200602','200601') note:it is in 'yyyymm' format
group by year_mnth

the benefits paid must be calculated for the year 200603 to 200601
but the display is as follows,

result
-------

year_mnth benefits_paid
200703 30000
200702 20000
200701 10000

no need for group by clause to see the whole years benefits paid.

Ans



I don't know that I precisely understand your question, but if you are wanting to group all months into a single year and display the amount of benefits paid for the year, this is a possible solution based on your example.

select substr(year_mnth,1,4) Year, sum(benefits_paid) Benefits
from tablename
where substr(year_mnth,1,4)='2006'
group by substr(year_mnth,1,4);

This would output:
Year Benefits
------ ------------
2006 60000

You can format this output in a variety of ways. I hope this helps.