retornar penúltima compra do cliente
01/09/2012
0
Pessoal,
Na query abaixo, preciso retornar a penúltima compra dos clientes. Porém não está correto, pois a consulta retorna a primeira compra ao invés da penúltima.
De forma isolada consigo retornar a ultima a penultima compra assim:
select CODCFO,max(DATAEMISSAO) as ultcompra
from TMOV where CODCFO = ''000675'' and CODTMV = ''2.2.01''
group by CODCFO
select top 1 CODCFO,DATAEMISSAO as penultcompra from tmov where CODCFO = ''000675'' and CODTMV = ''2.2.01'' and dataemissao <
(select max(DATAEMISSAO)
from TMOV where CODCFO = ''000675'' and CODTMV = ''2.2.01''
) order by DATAEMISSAO desc
Essa é a consulta:
select * from (
select *, datediff(day,penultcomp,ultcomp) as Dias from (
select *,
(select top 1 dataemissao from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and
t.codcfo = TGERAL.codcfo and t.codven1 =''0001''
and dataemissao < ultcomp) as penultcomp
from (select * from (select f.codcfo,nome,
(select max(dataemissao) as data from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and
t.codcfo = f.codcfo and
t.codven1 =''0001''
group by t.codcfo) as ultcomp
,(select max(valorliquido) from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and t.status <> ''C''
and t.codcfo = f.codcfo and
t.codven1 =''0001''
group by t.codcfo) as valor
,(select max(t.codven1) from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and t.status <> ''C''
and t.codcfo = f.codcfo and
t.codven1 =''0001''
group by t.codcfo) as codven
from fcfo f
left join fcfodef fd (NOLOCK) on (f.codcfo = fd.codcfo)
left join fcfocompl fc (NOLOCK) on (f.codcfo = fc.codcfo)
where nome is not null and ativo = 1 and fc.cliforne = ''001''
)T1)TGERAL)TAUX where datediff(day,penultcomp,ultcomp) >= 180)
TULT where
codven =''0001''
and ultcomp >= ''01/08/2012''
and ultcomp <= ''31/08/2012''
order by nome
Na query abaixo, preciso retornar a penúltima compra dos clientes. Porém não está correto, pois a consulta retorna a primeira compra ao invés da penúltima.
De forma isolada consigo retornar a ultima a penultima compra assim:
select CODCFO,max(DATAEMISSAO) as ultcompra
from TMOV where CODCFO = ''000675'' and CODTMV = ''2.2.01''
group by CODCFO
select top 1 CODCFO,DATAEMISSAO as penultcompra from tmov where CODCFO = ''000675'' and CODTMV = ''2.2.01'' and dataemissao <
(select max(DATAEMISSAO)
from TMOV where CODCFO = ''000675'' and CODTMV = ''2.2.01''
) order by DATAEMISSAO desc
Essa é a consulta:
select * from (
select *, datediff(day,penultcomp,ultcomp) as Dias from (
select *,
(select top 1 dataemissao from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and
t.codcfo = TGERAL.codcfo and t.codven1 =''0001''
and dataemissao < ultcomp) as penultcomp
from (select * from (select f.codcfo,nome,
(select max(dataemissao) as data from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and
t.codcfo = f.codcfo and
t.codven1 =''0001''
group by t.codcfo) as ultcomp
,(select max(valorliquido) from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and t.status <> ''C''
and t.codcfo = f.codcfo and
t.codven1 =''0001''
group by t.codcfo) as valor
,(select max(t.codven1) from tmov t WITH (NOLOCK) where codtmv = ''2.2.01'' and t.status <> ''C''
and t.codcfo = f.codcfo and
t.codven1 =''0001''
group by t.codcfo) as codven
from fcfo f
left join fcfodef fd (NOLOCK) on (f.codcfo = fd.codcfo)
left join fcfocompl fc (NOLOCK) on (f.codcfo = fc.codcfo)
where nome is not null and ativo = 1 and fc.cliforne = ''001''
)T1)TGERAL)TAUX where datediff(day,penultcomp,ultcomp) >= 180)
TULT where
codven =''0001''
and ultcomp >= ''01/08/2012''
and ultcomp <= ''31/08/2012''
order by nome
Victor Pavia
Curtir tópico
+ 0
Responder
Posts
01/09/2012
Joel Rodrigues
Cara, fiz um teste aqui com uma pequena tabela e o seguinte código funcionou. Acho que adaptando pode te servir:
Boa sorte.
SELECT TOP 1 * FROM VENDAS WHERE DATA < (SELECT MAX(DATA) FROM VENDAS WHERE CLIENTE = 123) AND CLIENTE = 123 ORDER BY DATA DESC
Boa sorte.
Responder
03/09/2012
Victor Pavia
ok. mas como faria para juntar as duas consultas?
PENULTIMA COMPRA
select top 1 codcfo,MAX(dataemissao) from TMOV where codcfo = '000675' and DATAEMISSAO < (
select MAX(DATAEMISSAO) from TMOV where codtmv = '2.2.01' and CODCFO = '000675' group by codcfo
) group by codcfo
ULTIMA COMPRA
select MAX(tm.DATAEMISSAO) from TMOV tm where tm.codtmv = '2.2.01' and tm.CODCFO = '000675' group by tm.codcfo
PENULTIMA COMPRA
select top 1 codcfo,MAX(dataemissao) from TMOV where codcfo = '000675' and DATAEMISSAO < (
select MAX(DATAEMISSAO) from TMOV where codtmv = '2.2.01' and CODCFO = '000675' group by codcfo
) group by codcfo
ULTIMA COMPRA
select MAX(tm.DATAEMISSAO) from TMOV tm where tm.codtmv = '2.2.01' and tm.CODCFO = '000675' group by tm.codcfo
Responder
03/09/2012
Alex Lekao
Ola Vitor, boa tarde!!!
O que voce precisa eh indicar uma detarminada compra e a compra anterior esta compra correto?
eu ja passei por esta situacao e precisava apresentar as datas da ultima e penultima compra de um cliente, no caso eu usei o script normal para apresentar a ultima compra e uma subselect onde eu apresentava a penultima compra, na subselect vc faz os links que vc precisa com a outra tabela normalmente.
vou postar abaixo o meu codigo para vc ver se entende o que fiz.
espero que ajude.
abraco.
SELECT
S.CODLOC1 AS LOCAL,
S.CODSTK AS CODIGO,
S.CODFAB AS NUMFAB,
S.MARCA AS FABRICA,
S.ESTOQUE AS ESTOQUE,
S.CLABCQTD AS ABC,
S.DTHCAD AS CADASTRO,
(SELECT COALESCE((CONVERT(VARCHAR(10),MAX(I2.DATEMI),103)),'01/01/1900')
FROM ITEM AS I2
LEFT JOIN VENDA AS R2 ON (R2.SID = I2.SID)
WHERE R2.TIPOREQ = 'VD'
AND R2.CODCPV <> '80'
AND I2.CODSTK = S.CODSTK
AND I2.DATEMI < (SELECT COALESCE((CONVERT(VARCHAR(10),MAX(I3.DATEMI),103)),'01/01/1900')
FROM ITEM AS I3
LEFT JOIN VENDA AS R3 ON (R3.SID = I3.SID)
WHERE R3.TIPOREQ = 'VD'
AND R3.CODCPV <> '80'
AND I3.CODSTK = I2.CODSTK)) PENULTVDA,
COALESCE((CONVERT(VARCHAR(10),MAX(I.DATEMI),103)),'01/01/1900') AS ULTVDA,
DATEDIFF(D,(SELECT COALESCE((CONVERT(VARCHAR(10),MAX(I2.DATEMI),103)),'01/01/1900')
FROM ITEM AS I2
LEFT JOIN VENDA AS R2 ON (R2.SID = I2.SID)
WHERE R2.TIPOREQ = 'VD'
AND R2.CODCPV <> '80'
AND I2.CODSTK = S.CODSTK
AND I2.DATEMI < (SELECT COALESCE((CONVERT(VARCHAR(10),MAX(I3.DATEMI),103)),'01/01/1900')
FROM ITEM AS I3
LEFT JOIN VENDA AS R3 ON (R3.SID = I3.SID)
WHERE R3.TIPOREQ = 'VD'
AND R3.CODCPV <> '80'
AND I3.CODSTK = I2.CODSTK)),(COALESCE((CONVERT(VARCHAR(10),MAX(I.DATEMI),103)),'01/01/1900'))) AS DIFERENCA
FROM ESTOQUE AS S
LEFT JOIN ITEM AS I ON (I.CODSTK = S.CODSTK)
LEFT JOIN VENDA AS R ON (R.SID = I.SID)
WHERE (1=1)
AND (R.CODCPV <> '80' )
AND (R.TIPOREQ = 'VD')
AND (S.ESTOQUE = VARIAVEL)
AND (S.CLABCQTD >= VARIAVEL)
AND (S.CLABCQTD <= VARIAVEL)
GROUP BY S.CODLOC1,S.CODSTK,S.CODFAB,S.MARCA,S.CLABCQTD,S.ESTOQUE,S.DTHCAD
ORDER BY S.CODSTK
O que voce precisa eh indicar uma detarminada compra e a compra anterior esta compra correto?
eu ja passei por esta situacao e precisava apresentar as datas da ultima e penultima compra de um cliente, no caso eu usei o script normal para apresentar a ultima compra e uma subselect onde eu apresentava a penultima compra, na subselect vc faz os links que vc precisa com a outra tabela normalmente.
vou postar abaixo o meu codigo para vc ver se entende o que fiz.
espero que ajude.
abraco.
SELECT
S.CODLOC1 AS LOCAL,
S.CODSTK AS CODIGO,
S.CODFAB AS NUMFAB,
S.MARCA AS FABRICA,
S.ESTOQUE AS ESTOQUE,
S.CLABCQTD AS ABC,
S.DTHCAD AS CADASTRO,
(SELECT COALESCE((CONVERT(VARCHAR(10),MAX(I2.DATEMI),103)),'01/01/1900')
FROM ITEM AS I2
LEFT JOIN VENDA AS R2 ON (R2.SID = I2.SID)
WHERE R2.TIPOREQ = 'VD'
AND R2.CODCPV <> '80'
AND I2.CODSTK = S.CODSTK
AND I2.DATEMI < (SELECT COALESCE((CONVERT(VARCHAR(10),MAX(I3.DATEMI),103)),'01/01/1900')
FROM ITEM AS I3
LEFT JOIN VENDA AS R3 ON (R3.SID = I3.SID)
WHERE R3.TIPOREQ = 'VD'
AND R3.CODCPV <> '80'
AND I3.CODSTK = I2.CODSTK)) PENULTVDA,
COALESCE((CONVERT(VARCHAR(10),MAX(I.DATEMI),103)),'01/01/1900') AS ULTVDA,
DATEDIFF(D,(SELECT COALESCE((CONVERT(VARCHAR(10),MAX(I2.DATEMI),103)),'01/01/1900')
FROM ITEM AS I2
LEFT JOIN VENDA AS R2 ON (R2.SID = I2.SID)
WHERE R2.TIPOREQ = 'VD'
AND R2.CODCPV <> '80'
AND I2.CODSTK = S.CODSTK
AND I2.DATEMI < (SELECT COALESCE((CONVERT(VARCHAR(10),MAX(I3.DATEMI),103)),'01/01/1900')
FROM ITEM AS I3
LEFT JOIN VENDA AS R3 ON (R3.SID = I3.SID)
WHERE R3.TIPOREQ = 'VD'
AND R3.CODCPV <> '80'
AND I3.CODSTK = I2.CODSTK)),(COALESCE((CONVERT(VARCHAR(10),MAX(I.DATEMI),103)),'01/01/1900'))) AS DIFERENCA
FROM ESTOQUE AS S
LEFT JOIN ITEM AS I ON (I.CODSTK = S.CODSTK)
LEFT JOIN VENDA AS R ON (R.SID = I.SID)
WHERE (1=1)
AND (R.CODCPV <> '80' )
AND (R.TIPOREQ = 'VD')
AND (S.ESTOQUE = VARIAVEL)
AND (S.CLABCQTD >= VARIAVEL)
AND (S.CLABCQTD <= VARIAVEL)
GROUP BY S.CODLOC1,S.CODSTK,S.CODFAB,S.MARCA,S.CLABCQTD,S.ESTOQUE,S.DTHCAD
ORDER BY S.CODSTK
Responder
04/09/2012
Victor Pavia
Fala Alex,
Então cara eu fiz essa query mas preciso retirar o parâmetro CODCFO(código do cliente), para pegar todos os clientes. Isso eu não estou conseguindo fazer.
select T1.CODCFO,f.nome,T1.ultcomp,T1.valor,T1.penultcomp, DATEDIFF(day,penultcomp,ultcomp) as dias from (
select tm.CODCFO,MAX(DATAEMISSAO) as ultcomp,valor, penultcomp from TMOV tm inner join
(select top 1 CODCFO,DATAEMISSAO as penultcomp from TMOV
where CODTMV = '2.2.01' and CODCFO = '000675'
and DATAEMISSAO < (select MAX(DATAEMISSAO) from TMOV
where CODTMV = '2.2.01' and CODCFO = '000675'
group by codcfo)
order by DATAEMISSAO desc)T1 on (T1.CODCFO = tm.CODCFO)
inner join
(select top 1 CODCFO,VALORLIQUIDO as valor from TMOV
where CODTMV = '2.2.01' and CODCFO = '000675'
and DATAEMISSAO = (select MAX(DATAEMISSAO) from TMOV
where CODTMV = '2.2.01' and CODCFO = '000675'
group by codcfo)
)T2 on (T2.CODCFO = tm.CODCFO)
where CODTMV = '2.2.01' and tm.CODCFO = '000675'
group by tm.codcfo,valor,penultcomp)T1
inner join
FCFO f on (f.CODCFO = T1.codcfo)
where ultcomp >= '01/08/2012' and ultcomp <= '31/08/2012'
Então cara eu fiz essa query mas preciso retirar o parâmetro CODCFO(código do cliente), para pegar todos os clientes. Isso eu não estou conseguindo fazer.
select T1.CODCFO,f.nome,T1.ultcomp,T1.valor,T1.penultcomp, DATEDIFF(day,penultcomp,ultcomp) as dias from (
select tm.CODCFO,MAX(DATAEMISSAO) as ultcomp,valor, penultcomp from TMOV tm inner join
(select top 1 CODCFO,DATAEMISSAO as penultcomp from TMOV
where CODTMV = '2.2.01' and CODCFO = '000675'
and DATAEMISSAO < (select MAX(DATAEMISSAO) from TMOV
where CODTMV = '2.2.01' and CODCFO = '000675'
group by codcfo)
order by DATAEMISSAO desc)T1 on (T1.CODCFO = tm.CODCFO)
inner join
(select top 1 CODCFO,VALORLIQUIDO as valor from TMOV
where CODTMV = '2.2.01' and CODCFO = '000675'
and DATAEMISSAO = (select MAX(DATAEMISSAO) from TMOV
where CODTMV = '2.2.01' and CODCFO = '000675'
group by codcfo)
)T2 on (T2.CODCFO = tm.CODCFO)
where CODTMV = '2.2.01' and tm.CODCFO = '000675'
group by tm.codcfo,valor,penultcomp)T1
inner join
FCFO f on (f.CODCFO = T1.codcfo)
where ultcomp >= '01/08/2012' and ultcomp <= '31/08/2012'
Responder
04/09/2012
Victor Pavia
Consegui resolver da uma olhada:
select *, DATEDIFF(DAY,penultcompra,ultcompra) AS dias from (
select tm.codcfo,f.nome,codven1,
(select MAX(DATAEMISSAO) from TMOV as t1 WITH (NOLOCK)
where CODTMV = '2.2.01' and t1.CODCFO = tm.CODCFO
and DATAEMISSAO < (select MAX(DATAEMISSAO) from TMOV WITH (NOLOCK) where CODTMV = '2.2.01' and CODCFO = tm.codcfo)) as penultcompra
,(select MAX(DATAEMISSAO) from TMOV t2 WITH (NOLOCK) where CODTMV = '2.2.01' and t2.CODCFO = tm.CODCFO) as ultcompra
,(select max(valorliquido) from TMOV t3 WITH (NOLOCK) where CODTMV = '2.2.01' and t3.CODCFO = tm.CODCFO
and DATAEMISSAO =(select MAX(DATAEMISSAO) from TMOV t2 WITH (NOLOCK) where CODTMV = '2.2.01' and t2.CODCFO = tm.CODCFO))as valor
from tmov tm WITH (NOLOCK) inner join FCFO f on (f.CODCFO = tm.CODCFO)
where codtmv = '2.2.01'
group by tm.CODCFO,f.nome,codven1)T1
WHERE ultcompra >= '01/08/2012'
and ultcompra <= '31/08/2012'
and CODVEN1 = '0001'
order by nome
Valew brother.
select *, DATEDIFF(DAY,penultcompra,ultcompra) AS dias from (
select tm.codcfo,f.nome,codven1,
(select MAX(DATAEMISSAO) from TMOV as t1 WITH (NOLOCK)
where CODTMV = '2.2.01' and t1.CODCFO = tm.CODCFO
and DATAEMISSAO < (select MAX(DATAEMISSAO) from TMOV WITH (NOLOCK) where CODTMV = '2.2.01' and CODCFO = tm.codcfo)) as penultcompra
,(select MAX(DATAEMISSAO) from TMOV t2 WITH (NOLOCK) where CODTMV = '2.2.01' and t2.CODCFO = tm.CODCFO) as ultcompra
,(select max(valorliquido) from TMOV t3 WITH (NOLOCK) where CODTMV = '2.2.01' and t3.CODCFO = tm.CODCFO
and DATAEMISSAO =(select MAX(DATAEMISSAO) from TMOV t2 WITH (NOLOCK) where CODTMV = '2.2.01' and t2.CODCFO = tm.CODCFO))as valor
from tmov tm WITH (NOLOCK) inner join FCFO f on (f.CODCFO = tm.CODCFO)
where codtmv = '2.2.01'
group by tm.CODCFO,f.nome,codven1)T1
WHERE ultcompra >= '01/08/2012'
and ultcompra <= '31/08/2012'
and CODVEN1 = '0001'
order by nome
Valew brother.
Responder
04/09/2012
Alex Lekao
Fala Victor, blz?
Legal camarada.
Voce usou o conceito do script que postei?
O problema das subselects é a performance que normalmente deixa lento, mas como vc usou uma tabela temporario a performance melhora bastante.
O seu script ficou melhor que o meu, vc usou a tabela temporaria, isso ajuda muito no desempenho.
Legal.
Abraco.
Legal camarada.
Voce usou o conceito do script que postei?
O problema das subselects é a performance que normalmente deixa lento, mas como vc usou uma tabela temporario a performance melhora bastante.
O seu script ficou melhor que o meu, vc usou a tabela temporaria, isso ajuda muito no desempenho.
Legal.
Abraco.
Responder
Clique aqui para fazer login e interagir na Comunidade :)
