I think you have an error in your scrip
since, seemingly , you compare the order code with the client code in join
, see inner join pedido b On
a.cod= b.cod
You can add AND
to your join
which will filter through MAX
version, see
select
a.cod,
a.nome,
b.pedido,
b.versao
from cliente a
inner join pedido b On a.cod= b.codigo_cliente AND b.versao = (select max(pedido.versao) from pedido where pedido.codigo_cliente = a.id)
order by b.pedido
For reference I made an example in SQL Fiddle
EDIT
What I'm seeing wrong are checks with isnull
, in them you say that if the field is null, it returns itself ( ISNULL (@vORCV_COD , @vORCV_COD)
) does not make sense of it.
Try the following query:
DECLARE
@vORCV_COD INT ,
@vDAT_INI CHAR(10),
@vDAT_FIM CHAR(10)
SET @vORCV_COD = null-- 333829
SET @vDAT_INI = '20180405' --data inicial
SET @vDAT_FIM = '20180501' -- data final
SELECT
A.ORCV_COD,
A.VEOV_VAL,
A.VEOV_QTT_VER ,
A.VEOV_PER_MARLUC,
E.PARC_COD,
A.PEDS_COD,
I.PEDS_DAT_CAD,
A.VEOV_DAT_CAD,
V.VEND_NOM_FAN,
B.AORC_OBS,
B.AORC_DAT_CAD,
C.USUA_NOM,
D.TAOR_NOM,
F.STOV_NOM,
ISNULL(G.PARC_NOM_RAZ, J.PAOV_NOM_EMP) AS 'RAZAO SOCIAL',
ISNULL(H.OROV_NOM, 'ORIGEM NÃO INDICADA') AS 'ORIGEM'
FROM TCOM_VERORV A
LEFT JOIN TCOM_ANDORC B ON A.ORCV_COD = B.ORCV_COD
LEFT JOIN TCOM_TIPANDORC D ON D.TAOR_COD = B.TAOR_COD
INNER JOIN TCOM_ORCVEN E ON E.ORCV_COD = A.ORCV_COD
INNER JOIN TCOM_STAORV F ON E.STOV_COD = F.STOV_COD
LEFT JOIN TACE_USUARIO C ON C.USUA_COD = B.USUA_COD
INNER JOIN TCOM_VENDEDOR V ON V.VEND_COD = E.VEND_COD
LEFT JOIN TCOM_PARCEIRO G ON G.PARC_COD = E.PARC_COD
LEFT JOIN TCOM_ORIORV H ON H.OROV_COD = E.OROV_COD
LEFT JOIN TCOM_PEDSAI I ON I.PEDS_COD = A.PEDS_COD
LEFT JOIN TCOM_PARORV J ON A.ORCV_COD = J.ORCV_COD
WHERE (1=1)
AND ISNULL(A.ORCV_COD, 0) = ISNULL (@vORCV_COD , ISNULL(A.ORCV_COD, 0))
AND A.VEOV_QTT_VER = (SELECT MAX(X.VEOV_QTT_VER) FROM TCOM_VERORV X WHERE ISNULL(X.ORCV_COD, 0) = ISNULL(A.ORCV_COD, 0))
AND CONVERT(CHAR(10), ORCV_DAT_CAD , 112) BETWEEN ISNULL (@vDAT_INI, CONVERT(CHAR(10), ORCV_DAT_CAD , 112)) AND ISNULL (@vDAT_FIM, CONVERT(CHAR(10), ORCV_DAT_CAD , 112))
Explanation:
In addition to clearing the indentation, the only thing I actually changed was where
in%, if it is null the variable it should compare with itself, and if the field is null, it will compare 0 with 0.