0
I am using this code to bring all calls from previous months being -1 last month, -2 the month before last month, but I need to bring the 3 results ( -1, -2 ,-3 ) which I must modify ?
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)
0
By the SQL of your post, I imagine you want to limit to the current year. If this is the case, it is not 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 process to deal with the logic of the dates. For example:
SET TERM ^ ;
create or alter Procedure MES_ANTERIOR ( INTEGER QUANTITY MES_ATUAL integer, ANO_ATUAL integer) Returns ( MES_ANTERIOR integer, ANO_ANTERIOR integer) as Begin ANO_ANTERIOR = ANO_ATUAL; MES_ANTERIOR = MES_ATUAL; WHILE (QUANTITY > 0) DO BEGIN MES_ANTERIOR = MES_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 ; ^
That 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))
)
Browser other questions tagged sql firebird-2.5
You are not signed in. Login or sign up in order to post.