I need to run a report that returns the values for the entire previous month. It used the following form:
where cliente.dtultcomp > trunc (SYSDATE-30)
But there are cases of the month having 28, 31 days for example. Is there a way to always bring me the full previous month regardless of how many days in the month or day of execution?
A simpler option would be to use the add_mouths function, which by passing a negative value subtracts months from the date, eg
...
where cliente.dtultcomp > add_months(sysdate, -1)
In this case you could use in WHERE to:
1. Compare current month - 1 with month of date
2. Compare the current year with the year of the date
This is easy using EXTRACT function:
The problem is in the case of month 1, which if subtracted would give 0 and the year should be subtracted as well. For this case, simple logic using CASE WHEN :
where (case when EXTRACT(month from sysdate) = 1
then 12
else EXTRACT(month from sysdate)-1 end) = EXTRACT(month from cliente.dtultcomp)
and (case when EXTRACT(month from sysdate) = 1
then EXTRACT(year from sysdate)-1
else EXTRACT(year from sysdate) end) = EXTRACT(year from cliente.dtultcomp)
This will resolve this: "return values for the entire previous month"
An example in SQL fiddle : link