cancel
Showing results for 
Search instead for 
Did you mean: 

Ayuda Query De nuevo

former_member203638
Active Contributor
0 Kudos

De este Tema

(Gracias Hector por cierto)


SELECT Distinct T0.DocDate, T0.DOcNum,
'Orden'=(Case When T1.BaseType Is NULL or T1.BaseType = -1 Then T0.U_Placa Else T1.BaseRef End),
T0.FolioNum Factura,T0.CardName Clientel
FROM OINV T0 
	INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
	INNER JOIN NNM1 T2 ON T0.Series = T2.Series 
WHERE T0.CreateDate BETWEEN '[%0]' AND '[%1]' and T0.Canceled='N' and T0.DocEntry NOT IN (
	Select BaseEntry FROM RIN1 WHERE BaseType != -1) and T2.SeriesName ='CCC'

Quiero que me de la fecha de la Orden de venta anexandole un:

INNER JOIN ORDR T3 ON T2.Series = T3.Series

Pero, al ejecutar el query me arroja en blanco el reporte.

Saludos y Gracias!

Alessandro.

Accepted Solutions (1)

Accepted Solutions (1)

former_member210784
Active Contributor
0 Kudos

Hola.

Intenta con este query:


SELECT Distinct T0.DocDate, T0.DOcNum,
T4.DocNum AS 'N° Orden', T4.DocDate,
T0.FolioNum Factura,T0.CardName Cliente
FROM OINV T0 
	INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
	INNER JOIN NNM1 T2 ON T0.Series = T2.Series 
	LEFT JOIN RDR1 T3 ON T1.BaseEntry = T3.DocEntry AND T3.LineNum = T1.BaseLine 
	AND T1.ItemCode = T3.ItemCode
	LEFT JOIN ORDR T4 ON T3.DocEntry = T4.DocEntry
WHERE T0.CreateDate BETWEEN '[%0]' AND '[%1]' and T0.Canceled='N' and T0.DocEntry NOT IN (
	Select BaseEntry FROM RIN1 WHERE BaseType != -1) and T2.SeriesName ='CCC'

Si todas las facturas tienen como documento base una Orden de Venta, debería funcionar ok. De lo contrario no serviría.

Nos cuenta cómo le va.

Saludos.

former_member203638
Active Contributor
0 Kudos

No, Pues.. Exelente!, ya quedo, muchas gracias!

Saludos.

Answers (1)

Answers (1)

former_member188440
Active Contributor
0 Kudos

Lo que creo que tienes que hacer es un left join con la ordr, ya que necesitas tomar como referencia la factura para saber qué fecha de cuál orden de venta quieres consultar.



SELECT Distinct T0.DocDate, T0.DOcNum,X.Docdate [Fecha pedido]
'Orden'=(Case When T1.BaseType Is NULL or T1.BaseType = -1 Then T0.U_Placa Else T1.BaseRef End),
T0.FolioNum Factura,T0.CardName Clientel


from ORDR X join rdr1 Y on X.docentry=Y.Docentry
left join inv1 T1 on Y.trgetentry =T1.docentry and Y.linenum=T1.baseline
left join oinv T0  on T1.docentry=T0.docentry
	INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
	INNER JOIN NNM1 T2 ON T0.Series = T2.Series 
WHERE T0.CreateDate BETWEEN '[%0]' AND '[%1]' and T0.Canceled='N' and T0.DocEntry NOT IN (
	Select BaseEntry FROM RIN1 WHERE BaseType != -1) and T2.SeriesName ='CCC'

A ver si te sirve asi