Firebird 2.5 Query of a trial

Asked

Viewed 138 times

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)

1 answer

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

You are not signed in. Login or sign up in order to post.