Buscar em uma tabela a data mais recente com MAX

WellingtonSilva · Julho 2, 2014

Bom dia,

Preciso de uma ajuda no seguinte:

Tenho a seguinte estrutura de tabelas:

Cliente

Compra

Compra_Produto

Fornecedor

Pedido

Pedido_Produto

Produto

Produto_Estoque

Preciso montar um select que me traga todas as informações cruzadas partindo da venda (Pedido) e dai alinhando com todo o rastro de informação existente, até ai tudo bem eu montei o seguinte SELECT:

select P.id as nrvenda, P.data_pedido as dt_vda, P.valor_pedido as vlr_venda, P.impostos_pedido as imp_tt_vda, 
	P.frete as frete_vda,
	PP.id_produto as codproduto, PP.qtde_produto as qtde_vendida, PP.valor_unitario as unit_vda_c_imp,
	(PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as unit_vda_s_imp,
	PP.valor_unitario - (PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as imp_unit,
	PD.produto, 
	C.id as codcli, C.nome_cliente as nome_cli,
	CP.id_compra as nrcompra, CP.valor_unitario as unit_s_imp, CR.data_compra
	from Pedido P
	inner join Pedido_Produto PP on PP.id_pedido = P.id
	inner join Produto PD on  PD.id = PP.id_produto
	inner join Cliente C on C.id = P.id_cliente
	inner join Compra_Produto CP on CP.id_produto = PD.id
	inner join Compra CR on CR.id = CP.id_compra

O resultado é o seguinte:

codproduto ..... nrcompra unit_s_imp data_compra

2 1 10.00 2014-06-30

4 2 45.00 2014-06-20

5 2 30.00 2014-06-20

8 3 8.00 2014-07-01

8 4 7.50 2014-06-10

É aqui que estou com problemas, vejam que o produto 8 aparece duas vezes, por ter duas notas de compra. Só que no meu resultado eu preciso que apareça somente a data mais recente, ou seja, 2014-07-01.

Tentei utilizar o MAX de várias maneiras mas não consegui chegar a um resultado.

Alguém por favor poderia me auxiliar.

Muito obrigado.

Motta · Julho 2, 2014

tente assim

select P.id as nrvenda, P.data_pedido as dt_vda, P.valor_pedido as vlr_venda, P.impostos_pedido as imp_tt_vda,
    P.frete as frete_vda,
    PP.id_produto as codproduto, PP.qtde_produto as qtde_vendida, PP.valor_unitario as unit_vda_c_imp,
    (PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as unit_vda_s_imp,
    PP.valor_unitario - (PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as imp_unit,
    PD.produto,
    C.id as codcli, C.nome_cliente as nome_cli,
    CP.id_compra as nrcompra, CP.valor_unitario as unit_s_imp, CR.data_compra
    from Pedido P
    inner join Pedido_Produto PP on PP.id_pedido = P.id
    inner join Produto PD on  PD.id = PP.id_produto
    inner join Cliente C on C.id = P.id_cliente
    INNER JOIN COMPRA_PRODUTO CP ON CP.ID_PRODUTO = PD.ID
    INNER JOIN COMPRA CR ON CR.ID = CP.ID_COMPRA
   where  CR.DATA_COMPRA = (SELECT MAX(CR2.DATA_COMPRA)
                         FROM COMPRA CR2
                         where cr2.id = cr.id)

WellingtonSilva · Julho 2, 2014

Olá Motta,

Eu já tentei desta maneira, inclusive até pegando um exemplo seu no Fórum, mas também não deu certo, ele continua trazendo as duas datas.

Eu tentei usar um LEFT OUTER JOIN com um SubSelect mas eu não consegui encontrar a sintaxe correta. Se é que isso é possível!!

Motta · Julho 2, 2014

Como Compra_Produto se realaciona com Compra , o "max" não deveria ser em alguma coluna de Compra_Produto ?

Outra solução pode ser colocar data_compra comom uma coluna via subselect (solução não muito boa) , mas ás vezes único jeito quando a coluna é "independente"

advaldomesquita · Julho 2, 2014

Tente algo assim:

Estou editando de um tablet, entao pode estar faltando algum campo no select principal.

[]´s

SELECT z.codproduto

, z.nrcompra

, z.unit_s_imp

, max(z.data_compra)

FROM

(

select P.id as nrvenda

, P.data_pedido as dt_vda

, P.valor_pedido as vlr_venda

, P.impostos_pedido as imp_tt_vda

, P.frete as frete_vda

, PP.id_produto as codproduto

, PP.qtde_produto as qtde_vendida

, PP.valor_unitario as unit_vda_c_imp

,(PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as unit_vda_s_imp

, PP.valor_unitario - (PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as imp_unit

, PD.produto

, C.id as codcli, C.nome_cliente as nome_cli

, CP.id_compra as nrcompra

, CP.valor_unitario as unit_s_imp

, CR.data_compra

from Pedido P

inner join Pedido_Produto PP on PP.id_pedido = P.id

inner join Produto PD on PD.id = PP.id_produto

inner join Cliente C on C.id = P.id_cliente

inner join Compra_Produto CP on CP.id_produto = PD.id

inner join Compra CR on CR.id = CP.id_compra

) z

GROUP BY z.codproduto, z.nrcompra , z.unit_s_imp

WellingtonSilva · Julho 2, 2014

A.Jr,

Também não deu, ele ainda trouxe as duas compras!

Deixa eu tentar ajudar, vou colocar abaixo o cenário resumido que eu tenho:

TABELA: Compra

CREATE TABLE [dbo].[Compra](
	[id] [int] NULL,
	[id_fornecedor] [int] NULL,
	[data_compra] [datetime] NULL,
	[valor_compra] [numeric](18, 2) NULL,
	[impostos_compra] [numeric](18, 2) NULL,
	[frete] [numeric](18, 2) NULL
) ON [PRIMARY]

insert into Compra (id,id_fornecedor,data_compra,valor_compra,impostos_compra,frete) values (1,2,'20140630',470.0,84.6,0)
insert into Compra (id,id_fornecedor,data_compra,valor_compra,impostos_compra,frete) values (2,2,'20140620',519.0,93.42,0)
insert into Compra (id,id_fornecedor,data_compra,valor_compra,impostos_compra,frete) values (3,2,'20140701',192.0,34.56,18.0)
insert into Compra (id,id_fornecedor,data_compra,valor_compra,impostos_compra,frete) values (4,2,'20140610',37.50,6.75,6.0)

Vejam que na tabela Compra eu tenho 4 compras cadastradas. Aqui eu só tenho o cabeçalho da compra.

TABELA: Compra_Produto

CREATE TABLE [dbo].[Compra_Produto](
	[id] [int] NULL,
	[id_compra] [int] NULL,
	[id_produto] [int] NULL,
	[qtde_produto] [numeric](6, 0) NULL,
	[valor_unitario] [numeric](18, 2) NULL,
	[imposto] [numeric](6, 2) NULL
) ON [PRIMARY]

select * from Compra_Produto

insert into Compra_Produto (id,id_compra,id_produto,qtde_produto,valor_unitario,imposto) values (1,1,1,10,12.0,18.0)
insert into Compra_Produto (id,id_compra,id_produto,qtde_produto,valor_unitario,imposto) values (2,2,4,5,45.0,18.0)
insert into Compra_Produto (id,id_compra,id_produto,qtde_produto,valor_unitario,imposto) values (3,3,7,12,8.0,18.0)
insert into Compra_Produto (id,id_compra,id_produto,qtde_produto,valor_unitario,imposto) values (4,3,8,12,8.0,18.0)
insert into Compra_Produto (id,id_compra,id_produto,qtde_produto,valor_unitario,imposto) values (5,4,8,5,7.5,18.0)

Na tabela Compra_Produto estão os dados dos produtos comprados, inclusive o ID da compra (id_compra).

Reparem que as compras 3 e 4 possuem o produto 8, onde cada compra foi efetuada em uma data diferente:

3 = 01/07/2014 / 4 = 10/06/2014

Então quando eu coloco o INNER destas duas tabelas no meu Select eu preciso trazer somente a última data de compra do produto 8.

Motta · Julho 2, 2014

Outra solução pode ser colocar data_compra comom uma coluna via subselect (solução não muito boa) , mas ás vezes único jeito quando a coluna é "independente"

ou uma function (algo do tipo retornar maior data de compra)

advaldomesquita · Julho 2, 2014

isso?

DECLARE @Compra TABLE (
[id] [int] NULL,
[id_fornecedor] [int] NULL,
[data_compra] [datetime] NULL,
[valor_compra] [numeric](18, 2) NULL,
[impostos_compra] [numeric](18, 2) NULL,
[frete] [numeric](18, 2) NULL
) -- ON [PRIMARY]
 
insert into @Compra (id,id_fornecedor,data_compra,valor_compra,impostos_compra,frete) values (1,2,'20140630',470.0,84.6,0)
insert into @Compra (id,id_fornecedor,data_compra,valor_compra,impostos_compra,frete) values (2,2,'20140620',519.0,93.42,0)
insert into @Compra (id,id_fornecedor,data_compra,valor_compra,impostos_compra,frete) values (3,2,'20140701',192.0,34.56,18.0)
insert into @Compra (id,id_fornecedor,data_compra,valor_compra,impostos_compra,frete) values (4,2,'20140610',37.50,6.75,6.0)
 
declare @Compra_Produto TABLE ( 
[id] [int] NULL,
[id_compra] [int] NULL,
[id_produto] [int] NULL,
[qtde_produto] [numeric](6, 0) NULL,
[valor_unitario] [numeric](18, 2) NULL,
[imposto] [numeric](6, 2) NULL
) -- ON [PRIMARY]
 
insert into @Compra_Produto (id,id_compra,id_produto,qtde_produto,valor_unitario,imposto) values (1,1,1,10,12.0,18.0)
insert into @Compra_Produto (id,id_compra,id_produto,qtde_produto,valor_unitario,imposto) values (2,2,4,5,45.0,18.0)
insert into @Compra_Produto (id,id_compra,id_produto,qtde_produto,valor_unitario,imposto) values (3,3,7,12,8.0,18.0)
insert into @Compra_Produto (id,id_compra,id_produto,qtde_produto,valor_unitario,imposto) values (4,3,8,12,8.0,18.0)
insert into @Compra_Produto (id,id_compra,id_produto,qtde_produto,valor_unitario,imposto) values (5,4,8,5,7.5,18.0)
 
 
-- MAX do ID_COMPRA por Produto
select * from (
SELECT MAX(id_Compra) as idcompra
, id_produto
FROM @Compra_Produto
group by id_produto
) p 
inner join @Compra_Produto cp on p.id_produto = cp.id_produto
and p.idcompra = cp.id_compra
inner join @Compra c on c.id = p.idcompra

WellingtonSilva · Julho 2, 2014

A.Jr,

O seu Select trouxe tudo, só que pelo id do produto.

O que eu estou tentando trazer são todos os produtos vendidos, com suas datas mais atuais de compra.

Exemplo:

Produto: 8

Data compra: 10/06/2014

Valor de compra: R$ 7,50

Produto: 8

Data compra: 01/07/2014

Valor de compra: R$ 8,00

O meu select tem de trazer somente o do dia 01/07/2014 e o valor de R$ 8,00, é neste valor que eu preciso trabalhar.

Veja meu select:

select CR.data_compra, CP.id_produto, CP.valor_unitario from Compra CR
	inner join Compra_Produto CP on CP.id_compra = CR.id
	inner join Pedido_Produto PP on PP.id_produto = CP.id_produto
	where CR.data_compra = (SELECT MAX(CR.data_compra) FROM Compra CR)
		and CP.id_produto = PP.id_produto

Eu usei o exemplo que o Motta passou e fiz este select reduzido, excluindo algumas tabelas como por exemplo Cliente (posso colocar depois).

Na minha tabela Pedido_Produto (vendas) existem - além do produto 8 - outros 3 (5, 2 e 4) produtos vendidos em diferentes pedidos de venda (o que não vem ao caso), só que o meu select acima só traz como resultado o produto 8, porque ele é a data mais atual, mas eu também preciso dos produtos 5, 2 e 4 com suas respectivas últimas datas de compra que são anteriores a 01/07/2014.

A tabela Pedido_Produto (produtos vendidos) - encurtado, seria isso:

declare @Pedido_Produto TABLE ( 
[id] [int] NULL,
[id_pedido] [int] NULL,
[id_produto] [int] NULL,
[qtde_produto] [numeric](6, 0) NULL,
[valor_unitario] [numeric](18, 2) NULL,
) -- ON [PRIMARY]

Consegui explicar ou piorei tudo!!!

Motta · Julho 2, 2014

Com subselect na coluna

select P.id as nrvenda, P.data_pedido as dt_vda, P.valor_pedido as vlr_venda, P.impostos_pedido as imp_tt_vda,
    P.frete as frete_vda,
    PP.id_produto as codproduto, PP.qtde_produto as qtde_vendida, PP.valor_unitario as unit_vda_c_imp,
    (PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as unit_vda_s_imp,
    PP.valor_unitario - (PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as imp_unit,
    PD.produto,
    C.id as codcli, C.nome_cliente as nome_cli,
    CP.ID_COMPRA as NRCOMPRA, CP.VALOR_UNITARIO as UNIT_S_IMP,
  (select max(CR2.DATA_COMPRA)
   from COMPRA CR2
   where cr2.id = cr.id) max_data_compra
    from Pedido P
    inner join Pedido_Produto PP on PP.id_pedido = P.id
    inner join Produto PD on  PD.id = PP.id_produto
    inner join Cliente C on C.id = P.id_cliente
    inner join COMPRA_PRODUTO CP on CP.ID_PRODUTO = PD.id
    inner join Compra CR on CR.id = CP.id_compra

WellingtonSilva · Julho 2, 2014

Meus amigos,

Ainda não virou, continua trazendo o produto repetido com datas diferentes.

Já tentei até concatenar os campos Produto e Data, mas também não resolveu!

Motta · Julho 2, 2014

Tem algo mais nestas linhas que faz que os registros se multipliquem eu acredito nestas duas tabelas de compra, como resolvo coisas assim , direto no BD faço esta query isolando um caso , um cliente , um pedido etc , faça isto com um select * , sem agrupamentos, subselects etc, e fica mais fácil identificar o problema.

WellingtonSilva · Julho 3, 2014

Bom dia Motta,

Ontem a noite fiz várias tentativas, mas nada surtiu efeito, até porque o MAX sempre vai retornar sempre um único valor, e eu preciso de vários "Max's".

Veja o que você e o A.Jr acham da minha sugestão dada à empresa:

Sugeri a criação de dois novos campos na tabela PRODUTO:

data_ult_compra

id_compra

Como no momento do cadastro da compra no sistema é feita a inserção dos produtos na tabela PRODUTO, poderíamos acrescentar estes dois campos na rotina de inserção, desta forma no momento da pesquisa cruzada entre Venda e Compra os dados da última compra sempre estarão atualizados na própria tabela PRODUTO.

O novo Select ficaria desta forma:

select	P.id as nrvenda, 
		P.data_pedido as dt_vda, 
		P.valor_pedido as vlr_venda, 
		P.impostos_pedido as imp_tt_vda, 
		P.frete as frete_vda,
		C.id as codcli, 
		C.nome_cliente as nome_cli,
		PP.id_produto as codproduto, 
		PD.produto,
		PP.qtde_produto as qtde_vendida, 
		PP.valor_unitario as unit_vda_c_imp,
		(PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as unit_vda_s_imp,
		PP.valor_unitario - (PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as imp_unit,
		PD.data_ult_compra,
		CR.id as nrcompra,
		PD.valor_unitario_compra
	from Pedido P
		inner join Pedido_Produto PP on PP.id_pedido = P.id
		inner join Produto PD on  PD.id = PP.id_produto
		inner join Cliente C on C.id = P.id_cliente
		inner join Compra CR on CR.id = PD.id_compra

O que vocês acham essa pode ser uma boa prática??

advaldomesquita · Julho 3, 2014

Bom dia srs.

Ontem eu estava mto atarefado e hoje estou um pouco menos, entao vamos lá.

Ontem a noite fiz várias tentativas, mas nada surtiu efeito, até porque o MAX sempre vai retornar sempre um único valor, e eu preciso de vários "Max's".

A função MAX precisa sempre de um agredador (Group by no seu caso) e se o dado esta repetindo é pq tem algum campo que você esta agrupando que tem valores diferentes. Creio que seja o valor de venda ou estoque. Vou dar uma olhada nas duas tabelas que esta no post #6 e #8 para verificar.

Como no momento do cadastro da compra no sistema é feita a inserção dos produtos na tabela PRODUTO, poderíamos acrescentar estes dois campos na rotina de inserção, desta forma no momento da pesquisa cruzada entre Venda e Compra os dados da última compra sempre estarão atualizados na própria tabela PRODUTO.

Mudar rotinas tanto em programação qto em banco é complicado dependendo da empresa.

Em particular acho mais simples resolver via t-sql do que alterar o modelo de negócios e/ou modelo do banco, pois querendo ou não vai precisar dar um update neste campo para manter historico, não?

Vou revisar o que esta escrito nos posts e dúvidas eu lhe procuro.

[]´s

Motta · Julho 3, 2014

Tente fazer os passos do post #12 como disse o A.Jr em geral a melhor solução é via SQL.

Onde trabalho tenho relatório complicados assim, as vezes faço colunas via functions de sql que , quando bem escritas , não geram tanta queda de performance.

Mas em todo o caso estude a viabilidade de uma trigger

WellingtonSilva · Julho 3, 2014

A.Jr,

Você tem razão na sua colocação quanto ao Max, na minha tabela o ID do produto aparece mais de uma vez com datas diferentes, acredito que por isso não esteja trazendo da forma que eu preciso. Também concordo com você e o Motta quanto a tentar resolver via SQL.

Tendo isso em mente fiz o seguinte:

Criei os 2 campos, (o qual eu já havia mencionado anteriormente), e ao invés de tentar alterar a aplicação eu criei uma Trigger (como o Motta sugeriu), vejam como ficou:

Campos novos

ALTER TABLE Produto ADD	data_ult_compra DATETIME,id_compra INT

Trigger

CREATE TRIGGER TGR_COMPRA_AI
ON Compra_Produto
FOR INSERT
AS
BEGIN
	DECLARE
	@IDCOMPRA	INT,
	@IDPRODUTO	INT,
	@VLR_U_CP	DECIMAL(18,2),
	@DATA_U_CP	DATETIME

	SELECT	@IDCOMPRA = id_compra,
			@IDPRODUTO = id_produto,
			@VLR_U_CP = valor_unitario
		FROM INSERTED

	SELECT @DATA_U_CP = ( SELECT data_compra FROM Compra WHERE id = @IDCOMPRA )

	UPDATE Produto 
		SET		valor_unitario_compra = @VLR_U_CP,
				data_ult_compra = @DATA_U_CP, 
				id_compra = @IDCOMPRA
		WHERE	id = @IDPRODUTO
END

Veja que criei a Trigger na tabela Compra_Produto, porque de lá eu já pego o id_produto, valor_unitario e o id_compra, com esse id_compra eu vou na tabela Compra e trago a data_compra.

Feito isso eu vou ter sempre a minha disposição as informações necessárias na pesquisa. Dai fiz o seguinte select:

select	P.id as nrvenda, 
		P.data_pedido as dt_vda, 
		P.valor_pedido as vlr_venda, 
		P.impostos_pedido as imp_tt_vda, 
		P.frete as frete_vda,
		C.id as codcli, 
		C.nome_cliente as nome_cli,
		PP.id_produto as codproduto, 
		PD.produto,
		PP.qtde_produto as qtde_vendida, 
		PP.valor_unitario as unit_vda_c_imp,
		(PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as unit_vda_s_imp,
		PP.valor_unitario - (PD.valor_unitario_compra + (PD.valor_unitario_compra * PD.markup) / 100) as imp_unit,
		PD.data_ult_compra,
		PD.id_compra as nrcompra,
		PD.valor_unitario_compra
	from Pedido P
		inner join Pedido_Produto PP on PP.id_pedido = P.id
		inner join Produto PD on  PD.id = PP.id_produto
		inner join Cliente C on C.id = P.id_cliente

advaldomesquita · Julho 3, 2014

Se a trigger resolveu, ótimo!!!

Mas cuidado com ela!!!!!!!!!!

Em ambientes criticos pode haver deadlock...

Aqui onde trabalho é proibido para ter uma ideia... Cursor entao... vixi...

WellingtonSilva · Julho 4, 2014

Valeu amigos,

Muito obrigado!

Arquivado

Buscar em uma tabela a data mais recente com MAX

Recommended Posts

WellingtonSilva 4

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

WellingtonSilva 4

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

WellingtonSilva 4

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

WellingtonSilva 4

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

WellingtonSilva 4

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

WellingtonSilva 4

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

WellingtonSilva 4

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

WellingtonSilva 4

Compartilhar este post

Link para o post

Compartilhar em outros sites

Fixar div até atingir uma certa altura

Ajuda com Extends e envio para o banco

PHP+Codeginiter - Orientação para Impressão

Fóruns

Tempo Real