I'm using this code to bring all the calls from the previous months being -1 last month, -2 the previous month to last month, however I need to bring the 3 results (-1, -2, 3) What should I change?
SELECT COUNT (*) FROM R_ATENDIMENTOS_TRIMESTRE (:I_ENTIDADE, :I_LOCAL)
where cast(substring(data_atendimento from 6 for 2) as integer) = cast(substring(CURRENT_DATE from 6 for 2) as integer) - 3
and extract(year from data_atendimento) = extract (year from current_date)
By the SQL of your post, I imagine you want to limit it to the current year. If this is the case, do not get difficult:
SELECT COUNT (*)
FROM R_ATENDIMENTOS_TRIMESTRE (:I_ENTIDADE, :I_LOCAL)
where extract(month from data_atendimento) between
extract(month from current_date) - 3 and
extract(month from current_date) - 1 and
extract(year from data_atendimento) =
extract (year from current_date)
If you want the previous months, regardless of the year, you will need to implement some procedure to handle the logic of the dates. For example:
SET TERM ^ ;
create or alter procedure MES_ANTERIOR ( QUANTITY integer, MES_ATUAL integer, ANO_ATUAL integer) returns MES_ANTERIOR integer, ANO_ANTERIOR integer) at begin ANO_ANTERIOR = ANO_AUAL; MES_ANTERIOR = MES_ATUAL; WHILE (QUANTITY > 0) DO BEGIN MES_ANTERIOR = MONTH_ANTERIOR - 1; if (MES_ANTERIOR = 0) then BEGIN MES_ANTERIOR = 12; ANO_ANTERIOR = ANO_ANTERIOR - 1; END QUANTITY = QUANTITY - 1; END suspend; end ^
SET TERM; ^
In this way, SQL could look like this:
select count(*)
from R_ATENDIMENTOS_TRIMESTRE (:I_ENTIDADE, :I_LOCAL)
where extract(year from data_atendimento) * 100 + extract(month from data_atendimento) between
(select ANO_ANTERIOR * 100 + MES_ANTERIOR from MES_ANTERIOR(
3,
extract(month from current_date),
extract(year from current_date))
) and
(select ANO_ANTERIOR * 100 + MES_ANTERIOR from MES_ANTERIOR(
1,
extract(month from current_date),
extract(year from current_date))
)