glufke.net

Selecionar tudo

SELECT 
A.CODCOLIGADA,
U.CODREDUZIDO,

TRUNC(
CASE WHEN D.CODCOMPRADOR =:COMPRADOR1 THEN AVG (B.DATACRIACAO - D.DATCOTACAO) ELSE 0 END , 2) AS PRZMEDCOTAC,
TRUNC(
CASE WHEN D.CODCOMPRADOR =:COMPRADOR2 THEN AVG (B.DATACRIACAO - D.DATCOTACAO) ELSE 0 END , 2) AS PRZMEDCOTAC2


FROM TITMMOV A 
INNER JOIN TITMMOVRATCCU T ON  T.CODCOLIGADA=A.CODCOLIGADA AND T.IDMOV=A.IDMOV AND T.NSEQITMMOV=A.NSEQITMMOV 
INNER JOIN GCCUSTO U ON T.CODCOLIGADA=U.CODCOLIGADA AND T.CODCCUSTO=U.CODCCUSTO
INNER JOIN TMOV B ON A.CODCOLIGADA=B.CODCOLIGADA AND A.IDMOV=B.IDMOV
LEFT OUTER JOIN TCCOTACAOITMMOV C ON A.CODCOLIGADA=C.CODCOLIGADA AND A.IDMOV=C.IDMOV AND A.NSEQITMMOV=C.NSEQITMMOV AND C.TIPOMOVCOMPRAS=1
LEFT OUTER JOIN TCCOTACAO D ON A.CODCOLIGADA=D.CODCOLIGADA AND D.CODCOTACAO=C.CODCOTACAO
LEFT OUTER JOIN TITMMOVRELAC E ON B.CODCOLIGADA=E.CODCOLORIGEM AND B.IDMOV=E.IDMOVORIGEM AND E.NSEQITMMOVORIGEM=A.NSEQITMMOV  
LEFT OUTER JOIN TITMMOV F ON F.CODCOLIGADA=A.CODCOLIGADA AND E.IDMOVDESTINO=F.IDMOV  AND E.NSEQITMMOVDESTINO=F.NSEQITMMOV
LEFT OUTER JOIN TMOV G ON G.CODCOLIGADA=F.CODCOLIGADA AND F.IDMOV=G.IDMOV 
LEFT OUTER JOIN TMOVRELAC H ON H.CODCOLORIGEM=E.CODCOLORIGEM AND H.IDMOVORIGEM=E.IDMOVORIGEM  AND H.IDMOVDESTINO=E.IDMOVDESTINO AND H.TIPORELAC='P'
LEFT OUTER JOIN TCCOTACAOITMMOV I ON F.CODCOLIGADA=I.CODCOLIGADA AND F.IDMOV=I.IDMOV AND F.NSEQITMMOV=I.NSEQITMMOV  AND D.CODCOTACAO=I.CODCOTACAO  AND I.TIPOMOVCOMPRAS=2

WHERE 
B.CODTMV = '1.1.01'
AND B.DATACRIACAO>= DATAINICIO
AND B.DATACRIACAO<= DATAFIM
AND G.STATUS<>'C'
GROUP BY A.CODCOLIGADA, U.CODREDUZIDO
ORDER BY U.CODREDUZIDO