Array
(
)

retornar penúltima compra do cliente

Victor Pavia
   - 01 set 2012

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

Joel Rodrigues
   - 01 set 2012

Cara, fiz um teste aqui com uma pequena tabela e o seguinte código funcionou. Acho que adaptando pode te servir:

#Código

SELECT TOP 1 * FROM VENDAS
WHERE DATA < (SELECT MAX(DATA) FROM VENDAS WHERE CLIENTE = 123) AND CLIENTE = 123
ORDER BY DATA DESC


Boa sorte.

Victor Pavia
   - 03 set 2012

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

Alex Lekao
   - 03 set 2012

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

Victor Pavia
   - 04 set 2012

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'

Victor Pavia
   - 04 set 2012

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.

Alex Lekao
   - 04 set 2012

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.