Consulta agrupando por mês

17/09/2004

0

Delphi

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?


Armando.boza

Armando.boza

Responder

Posts

17/09/2004

Vinicius2k

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

T+


Responder
Gostei + 0

17/09/2004

Vinicius2k

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 :
create view MINHAVIEW(DATA, MES) as
select CAMPO_DATA, extract(month from CAMPO_DATA) from TABELA;

e a instrução seria parecida com esta :
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 :
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+


Responder
Gostei + 0

18/09/2004

Armando.boza

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 : 
create view MINHAVIEW(DATA, MES) as
select CAMPO_DATA, extract(month from CAMPO_DATA) from TABELA;
e a instrução seria parecida com esta : 
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 : 
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


Responder
Gostei + 0

18/09/2004

Vinicius2k

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 :
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 :
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 :
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+


Responder
Gostei + 0

20/09/2004

Armando.boza

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 : 
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 : 
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 : 
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......


Responder
Gostei + 0

21/09/2004

Armando.boza

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[/code]


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?


Responder
Gostei + 0

21/09/2004

Vinicius2k

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 :
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+


Responder
Gostei + 0

22/09/2004

Armando.boza

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 : 
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


Responder
Gostei + 0

22/09/2004

Vinicius2k

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+


Responder
Gostei + 0

22/09/2004

Armando.boza

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


Responder
Gostei + 0

22/09/2004

Armando.boza

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?!?!?!?


Responder
Gostei + 0

22/09/2004

Vinicius2k

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 [b:8a4e25ab27]DATE[/b:8a4e25ab27]
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 :
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+


Responder
Gostei + 0

22/09/2004

Armando.boza

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 [b:acf0d28557]DATE[/b:acf0d28557] 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 : 
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


Responder
Gostei + 0

22/09/2004

Armando.boza

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


Responder
Gostei + 0

Assista grátis a nossa aula inaugural

Assitir aula

Saiba por que programar é uma questão de
sobrevivência e como aprender sem riscos

Assistir agora

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar