Array
(
)

Consulta agrupando por mês

Armando.boza
   - 17 set 2004

olá pessoal...

tenho uma tabela com vários registros e cada registro tem seu campo de DATA no format ex: ´02/01/2004´ ....
quero montar um relatório que agrupe quantidade de registros por mês num determinado intervalo de datas ou meses mesmo.
Ex. Janeiro 3000
Fevereiro 3500
Março 4000

Alguem pode me ajudar?


Vinicius2k
   - 17 set 2004

Qual o banco Armando?
A coluna DATA é tipo DATE ou TIMESTAMP?

T+


Vinicius2k
   - 17 set 2004

Verifiquei em outos tópicos seus e me parece q vc trabalha com IB 6... ainda é ele?
Se for, o IB 6 não agrupa por função, então uma view auxiliar vai ser necessária :
#Código

create view MINHAVIEW(DATA, MES) as
select CAMPO_DATA, extract(month from CAMPO_DATA) from TABELA;

e a instrução seria parecida com esta :
#Código
select MES, count(*) as QUANTIDADE
from MINHAVIEW
where DATA between ´2004-01-01´ and ´2004-12-31´
group by MES


Não me recordo se o FB 1.0 já agrupa por função... se não vc precisará do mesmo procedimento, mas se já estiver usando o FB 1.5, a instrução pode ser parecida com esta :
#Código
select extract(month from CAMPO_DATA) as MES, count(*) as QUANTIDADE
from TABELA
where CAMPO_DATA between ´2004-01-01´ and ´2004-12-31´
group by extract(month from CAMPO_DATA)


Espero ter ajudado...

T+


Armando.boza
   - 18 set 2004


Citação:
Verifiquei em outos tópicos seus e me parece q vc trabalha com IB 6... ainda é ele?
Se for, o IB 6 não agrupa por função, então uma view auxiliar vai ser necessária :
#Código

create view MINHAVIEW(DATA, MES) as
select CAMPO_DATA, extract(month from CAMPO_DATA) from TABELA;

e a instrução seria parecida com esta :
#Código
select MES, count(*) as QUANTIDADE
from MINHAVIEW
where DATA between ´2004-01-01´ and ´2004-12-31´
group by MES


Não me recordo se o FB 1.0 já agrupa por função... se não vc precisará do mesmo procedimento, mas se já estiver usando o FB 1.5, a instrução pode ser parecida com esta :
#Código
select extract(month from CAMPO_DATA) as MES, count(*) as QUANTIDADE
from TABELA
where CAMPO_DATA between ´2004-01-01´ and ´2004-12-31´
group by extract(month from CAMPO_DATA)


Espero ter ajudado...

T+

Olá amigo.... putz.. me desculpe, esqueci de comentar o banco e o tipo do campo.... é o Firebird 1.5 e o campo é DATE... e tava querendo da seguinte forma : tipo ´06/2004´...

Dei a seguinte select....

SELECT SUBSTR(DATAFICHA,1,7) AS DATA,COUNT(*) FROM FICHAS GROUP BY 1

E deu certo só que a data aparece neste formato: 2004/03 ... com o ano antes


Vinicius2k
   - 18 set 2004

Esse efeito é por causa do padrão de datas no IB/FB que é AAAA-MM-DD...
Bem, já que vc está com o FB 1.5, temos mais soluções possíveis... percebo agora 3 :

1. Usando a SUBSTRING (não é a UDF q vc está usando, esta é ´nativa´ do FB 1.5) e concatenando :
#Código

select
substring(CAMPO_DATA from 6 for 2)||´/´||substring(CAMPO_DATA from 1 for 4) as MES_ANO,
count(*) as QUANTIDADE
from
TABELA
where
CAMPO_DATA between ´2004-01-01´ and ´2005-12-31´
group by
1


2. Usando transformação de INTEGER para VARCHAR e concatenando :
#Código
select
cast( extract(month from CAMPO_DATA) as varchar(2) ) ||´/´||
cast( extract(year from CAMPO_DATA) as varchar(4) ) as MES_ANO,
count(*) as QUANTIDADE
from
TABELA
where
CAMPO_DATA between ´2004-01-01´ and ´2005-12-31´
group by
1


3. Não concatenando e mantendo as colunas como INTEGER :
#Código
select
extract(month from CAMPO_DATA) as MES,
extract(year from CAMPO_DATA) as ANO,
count(*) as QUANTIDADE
from
TABELA
where
CAMPO_DATA between ´2004-01-01´ and ´2005-12-31´
group by
1, 2


Talvez tenha mais alguma, mas acho q agora vc já tem um ponto de partita...

T+


Armando.boza
   - 20 set 2004


Citação:
Esse efeito é por causa do padrão de datas no IB/FB que é AAAA-MM-DD...
Bem, já que vc está com o FB 1.5, temos mais soluções possíveis... percebo agora 3 :

1. Usando a SUBSTRING (não é a UDF q vc está usando, esta é ´nativa´ do FB 1.5) e concatenando :
#Código

select
substring(CAMPO_DATA from 6 for 2)||´/´||substring(CAMPO_DATA from 1 for 4) as MES_ANO,
count(*) as QUANTIDADE
from
TABELA
where
CAMPO_DATA between ´2004-01-01´ and ´2005-12-31´
group by
1


2. Usando transformação de INTEGER para VARCHAR e concatenando :
#Código
select
cast( extract(month from CAMPO_DATA) as varchar(2) ) ||´/´||
cast( extract(year from CAMPO_DATA) as varchar(4) ) as MES_ANO,
count(*) as QUANTIDADE
from
TABELA
where
CAMPO_DATA between ´2004-01-01´ and ´2005-12-31´
group by
1


3. Não concatenando e mantendo as colunas como INTEGER :
#Código
select
extract(month from CAMPO_DATA) as MES,
extract(year from CAMPO_DATA) as ANO,
count(*) as QUANTIDADE
from
TABELA
where
CAMPO_DATA between ´2004-01-01´ and ´2005-12-31´
group by
1, 2


Talvez tenha mais alguma, mas acho q agora vc já tem um ponto de partita...

T+

Amigo, primeiro quero agradecer o seu empenho em me ajudar.... é difícil achar pessoas assim nos fóruns da internet.... muito obrigado mesmo....

Sua dica funcionou perfeitamente, muito obrigado mesmo......


Armando.boza
   - 21 set 2004


Citação:
substring(CAMPO_DATA from 6 for 2)||´/´||substring(CAMPO_DATA from 1 for 4) as MES_ANO,
count(*) as QUANTIDADE
from
TABELA
where
CAMPO_DATA between ´2004-01-01´ and ´2005-12-31´
group by
1[/tagcod]


Amigo, utilizei o esquema acima para fazer uma nova pesquisa, só que diária agora (não por mês como eu tinha colocado).....

só que notei que quando a pesquisa é feita com 2 meses o resultado sai assim..

01/05/2004 ....
01/06/2004 ....
02/05/2004 ....
02/06/2004 ....

e se eu simplesmente faço agrupar direto sem o substring ele retorna as datas todas inversas... 2004-05-01...

o que posso fazer?


Vinicius2k
   - 21 set 2004

Armando,

Aonde vc está visualizando os dados?
Não tem como contornar... este é o formato de armazenamento de datas do IB/FB, porém quando utilizados componentes data-awares, ligados aos TFields do tipo TDate, TDateTime, TSQLTimeStamp, essa conversão é transparente... vc visualiza os tipo Date da forma convencional, inclusive respeitando as configurações regionais da estação...
Mesmo que vc não use data-awares, para extrair um valor de uma coluna da query, bastaria utilizar :
#Código

var SuaVariavel: TDateTime;
SuaVariavel:= SuaQuery.FieldByName(´CAMPO_DATA´).AsDateTime


Dê-nos mais detalhes de onde e como vc está utilziando o resultado desta query para que possamos ajudá-lo mais...

PS: Se vc quer utilizar agora uma data completa e não somente mes/ano, desaconselho a continuar utilizando substrings e concatenações... não são necessárias.

T+


Armando.boza
   - 22 set 2004


Citação:
Armando,

Aonde vc está visualizando os dados?
Não tem como contornar... este é o formato de armazenamento de datas do IB/FB, porém quando utilizados componentes data-awares, ligados aos TFields do tipo TDate, TDateTime, TSQLTimeStamp, essa conversão é transparente... vc visualiza os tipo Date da forma convencional, inclusive respeitando as configurações regionais da estação...
Mesmo que vc não use data-awares, para extrair um valor de uma coluna da query, bastaria utilizar :
#Código

var SuaVariavel: TDateTime;
SuaVariavel:= SuaQuery.FieldByName(´CAMPO_DATA´).AsDateTime


Dê-nos mais detalhes de onde e como vc está utilziando o resultado desta query para que possamos ajudá-lo mais...

PS: Se vc quer utilizar agora uma data completa e não somente mes/ano, desaconselho a continuar utilizando substrings e concatenações... não são necessárias.

T+


Amigo.....

É assim... tenho o relatório que é MES/ANO e este que é DIA/MES/ANO ...

O MES/ANO tá perfeito...

Eles são exibidos em uma dbgrid e se o funcionário quiser tem a impressão em quickreport ...
O relatório é feito através de STRORED PROCEDURE ... direto no banco, para melhor desempenho.....

fica assim:

procedure TFRMFichas.BitBtn13Click(Sender: TObject);
begin
with QRYPeriododia do begin
close;
sql.clear;
params.clear;
sql.add(´SELECT * FROM SP_PERIODODIA(:DATA1,:DATA2) ORDER BY DATA)´);
parambyname(´DATA1´).asdate := strtodate(MaskEdit29.text);
parambyname(´DATA2´).asdate := strtodate(MaskEdit30.text);
open;
end;
SpeedButton1.Enabled := true;

end;

Esse é o comando que chamo o PROCEDURE... o resultado vai direto para a dbgrid através de um DATASOURCE .....

O procedure é este:

SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE ´SP_PERIODODIA´
(
´DATAINICIAL´ DATE,
´DATAFINAL´ DATE
)
RETURNS
(
´DATA´ VARCHAR(10),
´PSM´ INTEGER,
´PSC´ INTEGER,
´PSP´ INTEGER,
´PSORT´ INTEGER,
´TOTAL´ INTEGER
)
AS
BEGIN EXIT; END ^


ALTER PROCEDURE ´SP_PERIODODIA´
(
´DATAINICIAL´ DATE,
´DATAFINAL´ DATE
)
RETURNS
(
´DATA´ VARCHAR(10),
´PSM´ INTEGER,
´PSC´ INTEGER,
´PSP´ INTEGER,
´PSORT´ INTEGER,
´TOTAL´ INTEGER
)
AS
DECLARE VARIABLE ITOTALPSM INTEGER;
DECLARE VARIABLE ITOTALPSC INTEGER;
DECLARE VARIABLE ITOTALPSP INTEGER;
DECLARE VARIABLE ITOTALPSORT INTEGER;
DECLARE VARIABLE ITOTALGERAL INTEGER;

BEGIN

/*Agrupa as informações por tipo de internação*/
FOR SELECT SUBSTRING(DATAFICHA FROM 9 FOR 2)||´/´||SUBSTRING(DATAFICHA FROM 6 FOR 2)||´/´||SUBSTRING(DATAFICHA FROM 1 FOR 4)
FROM FICHAS
WHERE CAST(FICHAS.dataficha AS DATE) BETWEEN :datainicial AND :datafinal
GROUP BY 1
INTO :DATA
DO
BEGIN

/* Inicializa as variáveis*/


ITOTALPSM = 0;
ITOTALPSC = 0;
ITOTALPSP = 0;
ITOTALPSORT = 0;
ITOTALGERAL = 0;


/* Total de fichas por especialidade*/
SELECT COUNT(*)
FROM FICHAS
WHERE CAST(FICHAS.dataficha AS DATE) BETWEEN :datainicial AND :datafinal
AND SUBSTRING(DATAFICHA FROM 9 FOR 2)||´/´||SUBSTRING(DATAFICHA FROM 6 FOR 2)||´/´||SUBSTRING(DATAFICHA FROM 1 FOR 4) = :DATA
AND ESPFICHA = ´PSM´
INTO :ITOTALPSM;

SELECT COUNT(*)
FROM FICHAS
WHERE CAST(FICHAS.dataficha AS DATE) BETWEEN :datainicial AND :datafinal
AND SUBSTRING(DATAFICHA FROM 9 FOR 2)||´/´||SUBSTRING(DATAFICHA FROM 6 FOR 2)||´/´||SUBSTRING(DATAFICHA FROM 1 FOR 4) = :DATA
AND ESPFICHA = ´PSC´
INTO :ITOTALPSC;

SELECT COUNT(*)
FROM FICHAS
WHERE CAST(FICHAS.dataficha AS DATE) BETWEEN :datainicial AND :datafinal
AND SUBSTRING(DATAFICHA FROM 9 FOR 2)||´/´||SUBSTRING(DATAFICHA FROM 6 FOR 2)||´/´||SUBSTRING(DATAFICHA FROM 1 FOR 4) = :DATA
AND ESPFICHA = ´PSP´
INTO :ITOTALPSP;

SELECT COUNT(*)
FROM FICHAS
WHERE CAST(FICHAS.dataficha AS DATE) BETWEEN :datainicial AND :datafinal
AND SUBSTRING(DATAFICHA FROM 9 FOR 2)||´/´||SUBSTRING(DATAFICHA FROM 6 FOR 2)||´/´||SUBSTRING(DATAFICHA FROM 1 FOR 4) = :DATA
AND ESPFICHA = ´PSORT´
INTO :ITOTALPSORT;



/* Composição*/
PSM = ITOTALPSM;
PSC = ITOTALPSC;
PSP = ITOTALPSP;
PSORT = ITOTALPSORT;
TOTAL = ITOTALPSM+ITOTALPSP+ITOTALPSC+ITOTALPSORT;


SUSPEND;
END
END
^

SET TERM ; ^

Acho que agora sim.... tá tudo ai


Vinicius2k
   - 22 set 2004

Armando,
Agora vc pegou no meu ponto fraco que são SPs, mas se vc está exibindo em data-aware (DBGrid), creio que o q vc deve fazer é alterar o tipo da variável DATA no retorno da SP, para DATE e retirar as substrings e concatenações, agrupando diretamente pela data...
Com isso, suponho, que a exibição no Grid será convertida para o formato convencional de datas de forma transparente...

T+


Armando.boza
   - 22 set 2004


Citação:
Armando,
Agora vc pegou no meu ponto fraco que são SPs, mas se vc está exibindo em data-aware (DBGrid), creio que o q vc deve fazer é alterar o tipo da variável DATA no retorno da SP, para DATE e retirar as substrings e concatenações, agrupando diretamente pela data...
Com isso, suponho, que a exibição no Grid será convertida para o formato convencional de datas de forma transparente...

T+


BLZ irmão .... vou tentar fazer algumas alterações.....

Muito obrigado


Armando.boza
   - 22 set 2004

Amigo... veja só..

Quando dou uma simples SELECT DATAFICHA FROM FICHAS e retorno na dbgrid a data volta certinho (01-12-2004).....

Quando dou a select na SP a data retorna ao contrário....

Que coisa não?!?!?!?


Vinicius2k
   - 22 set 2004

Armando,

Eu montei aqui sua SP, e consegui o resultado que vc deseja, seguindo os passos de que eu já havia lhe falado :
1. Mudei o tipo de dado do parametro de retorno DATA de VARCHAR para DATE
2. Retirei todos os CASTs e SUBSTRINGs, afinal vc está trabalhando com datas inteiras agora não são necessários (desde que seu campo DATAFICHA seja do tipo DATE).

Se vc manter o retorno do parametro DATA como VARCHAR o retorno é no formato padrão do IB/FB (AAAA-MM-DD) e, inclusive, seu TField é tipo TStringField...

Esta é a SP que criei para simulação, observe a mudança do tipo de dado do parametro DATA e a retirada dos CASTs e SUBSTRINGs :
#Código

SET TERM ^ ;

CREATE PROCEDURE SP_PERIODODIA (
DATAINICIAL DATE,
DATAFINAL DATE)
RETURNS (
DATA DATE,
PSM INTEGER,
PSC INTEGER,
PSP INTEGER,
PSORT INTEGER,
TOTAL INTEGER)
AS
DECLARE VARIABLE ITOTALPSM INTEGER;
DECLARE VARIABLE ITOTALPSC INTEGER;
DECLARE VARIABLE ITOTALPSP INTEGER;
DECLARE VARIABLE ITOTALPSORT INTEGER;
DECLARE VARIABLE ITOTALGERAL INTEGER;
BEGIN

/*Agrupa as informações por tipo de internação*/
FOR SELECT TESTE2
FROM TESTE
WHERE TESTE.TESTE2 BETWEEN :datainicial AND :datafinal
GROUP BY 1
INTO :DATA
DO
BEGIN

/* Inicializa as variáveis*/
ITOTALPSM = 0;
ITOTALPSC = 0;
ITOTALPSP = 0;
ITOTALPSORT = 0;
ITOTALGERAL = 0;

/* Total de fichas por especialidade*/
SELECT COUNT(*)
FROM TESTE
WHERE TESTE.TESTE2 BETWEEN :datainicial AND :datafinal AND TESTE.TESTE2 = :DATA
INTO :ITOTALPSM;

SELECT COUNT(*)
FROM TESTE
WHERE TESTE.TESTE2 BETWEEN :datainicial AND :datafinal AND TESTE.TESTE2 = :DATA
INTO :ITOTALPSC;

SELECT COUNT(*)
FROM TESTE
WHERE TESTE.TESTE2 BETWEEN :datainicial AND :datafinal AND TESTE.TESTE2 = :DATA
INTO :ITOTALPSP;

SELECT COUNT(*)
FROM TESTE
WHERE TESTE.TESTE2 BETWEEN :datainicial AND :datafinal AND TESTE.TESTE2 = :DATA
INTO :ITOTALPSORT;

/* Composição*/
PSM = ITOTALPSM;
PSC = ITOTALPSC;
PSP = ITOTALPSP;
PSORT = ITOTALPSORT;
TOTAL = ITOTALPSM+ITOTALPSP+ITOTALPSC+ITOTALPSORT;

SUSPEND;
END
END
^

SET TERM ; ^


T+


Armando.boza
   - 22 set 2004


Citação:
Armando,

Eu montei aqui sua SP, e consegui o resultado que vc deseja, seguindo os passos de que eu já havia lhe falado :
1. Mudei o tipo de dado do parametro de retorno DATA de VARCHAR para DATE
2. Retirei todos os CASTs e SUBSTRINGs, afinal vc está trabalhando com datas inteiras agora não são necessários (desde que seu campo DATAFICHA seja do tipo DATE).

Se vc manter o retorno do parametro DATA como VARCHAR o retorno é no formato padrão do IB/FB (AAAA-MM-DD) e, inclusive, seu TField é tipo TStringField...

Esta é a SP que criei para simulação, observe a mudança do tipo de dado do parametro DATA e a retirada dos CASTs e SUBSTRINGs :
#Código

SET TERM ^ ;

CREATE PROCEDURE SP_PERIODODIA (
DATAINICIAL DATE,
DATAFINAL DATE)
RETURNS (
DATA DATE,
PSM INTEGER,
PSC INTEGER,
PSP INTEGER,
PSORT INTEGER,
TOTAL INTEGER)
AS
DECLARE VARIABLE ITOTALPSM INTEGER;
DECLARE VARIABLE ITOTALPSC INTEGER;
DECLARE VARIABLE ITOTALPSP INTEGER;
DECLARE VARIABLE ITOTALPSORT INTEGER;
DECLARE VARIABLE ITOTALGERAL INTEGER;
BEGIN

/*Agrupa as informações por tipo de internação*/
FOR SELECT TESTE2
FROM TESTE
WHERE TESTE.TESTE2 BETWEEN :datainicial AND :datafinal
GROUP BY 1
INTO :DATA
DO
BEGIN

/* Inicializa as variáveis*/
ITOTALPSM = 0;
ITOTALPSC = 0;
ITOTALPSP = 0;
ITOTALPSORT = 0;
ITOTALGERAL = 0;

/* Total de fichas por especialidade*/
SELECT COUNT(*)
FROM TESTE
WHERE TESTE.TESTE2 BETWEEN :datainicial AND :datafinal AND TESTE.TESTE2 = :DATA
INTO :ITOTALPSM;

SELECT COUNT(*)
FROM TESTE
WHERE TESTE.TESTE2 BETWEEN :datainicial AND :datafinal AND TESTE.TESTE2 = :DATA
INTO :ITOTALPSC;

SELECT COUNT(*)
FROM TESTE
WHERE TESTE.TESTE2 BETWEEN :datainicial AND :datafinal AND TESTE.TESTE2 = :DATA
INTO :ITOTALPSP;

SELECT COUNT(*)
FROM TESTE
WHERE TESTE.TESTE2 BETWEEN :datainicial AND :datafinal AND TESTE.TESTE2 = :DATA
INTO :ITOTALPSORT;

/* Composição*/
PSM = ITOTALPSM;
PSC = ITOTALPSC;
PSP = ITOTALPSP;
PSORT = ITOTALPSORT;
TOTAL = ITOTALPSM+ITOTALPSP+ITOTALPSC+ITOTALPSORT;

SUSPEND;
END
END
^

SET TERM ; ^


T+


Cara.... que mancada minha... ainda bem que vc viu.... por isso é bom ter amigos na net... muito obrigado...
Vou testar e já dou retorno...
Tanks


Armando.boza
   - 22 set 2004

Vinicius

Muito obrigado...

Era só aquele detalhe mesmo... de varchar para DATE...

Agora tá funcionando perfeitamente...

Agradeço sua atenção e se um dia eu puder te ajudar estarei sempre a dispodição...ok

Abraço amigo