Buenos días
Alguien tendrá alguna idea de como hacerle para obtener la ultima fecha de una orden de venta?
para poderme darme a entender, anexo la imagen del resultado de la consulta.
En la columna Fecha Fact Ame tengo dos fechas, 3/03 y 28/02, la Orden de Venta es la misma.
Pensé que ya lo había logrado en un tema anterior :(, pero luego surgieron mas casos.
Este es el ultimo query que tengo.
SELECT DISTINCT
T0.DocNum as 'OV'
,T0.U_Convenio as 'Conv OV'
,T11.U_Convenio as 'Conv Fact'
,T0.SlpCode as 'Vendedor'
,T11.[Total Fact] as 'Total Fact'
,T11.Vendedor as 'Vendedor Fact'
,T0.U_TIPO_Convenio as 'Tipo Conv OV'
,T18.isIns as 'FR'
,T18.IsICT as 'FD+P'
--, T11.DocNum as 'FAC'
--, T0.DocTotal as 'Total OV'
, IIF(T0.DocTotal = T0.PaidToDate,T0.DocTotal,0) as 'Tot OV'
--, T11.DocDate as 'Fecha FACT'
, T15.DocDate as 'Fecha Pago'
--, T15.PagadoFact as 'Total Pago'
, T18.FechaFactAme as 'Fecha Fact ame'
,T18.FechaEnt as 'FechaEnt'
, T17.U_Name as 'Usuario'
, T0.DocDate AS 'DocDate_OV'
, T0.DocDueDate AS 'DocDueDate_OV'
, T0.TaxDate AS 'TaxDate_OV'
, T0.NumAtCard AS 'NumAtCard_OV'
, T0.Comments AS 'Comments_OV'
, T0.PickRmrk AS 'PickRmrk_OV'
, CASE T0.DocStatus WHEN 'O' THEN 'Abierto' WHEN 'C' THEN 'Cerrado' END AS 'ESTADO_OV'
, T0.AtcEntry AS 'AtcEntry_OV'
, FORMAT(T0.CreateTs, '00:00:00') AS 'HORA_OV'
,t14.SlpName as 'Asesor'
FROM
[ORDR] T0 /*OV*/
INNER JOIN [RDR1] T1 ON T0.DocEntry = T1.DocEntry /*OV*/
LEFT JOIN [INV1] T10 ON T0.DocEntry = T10.BaseEntry AND T0.ObjType = T10.BaseType AND T1.LineNum = T10.BaseLine
--LEFT JOIN [OINV] T11 ON T10.DocEntry = T11.DocEntry
---T11---
LEFT JOIN (SELECT T2.isIns, T2.IsICT, 2.DocNum, T2.DocEntry, t2.U_Convenio,T2.U_TIPO_Convenio,T2.DocTotal 'Total Fact', T2.SlpCode as 'Vendedor'
FROM OINV T2
WHERE T2.CANCELED='N' and T2.U_Convenio='SI') T11 ON T10.DocEntry = T11.DocEntry
LEFT JOIN [OCRD] T13 ON T0.CardCode = T13.CardCode
LEFT JOIN [OSLP] T14 ON T0.SlpCode = T14.SlpCode
-----T15----
LEFT JOIN (SELECT T1.DocEntry, T1.InvType, SUM(T1.SumApplied) as PagadoFact, MAX(DocDate) as DocDate
FROM [ORCT] T0 JOIN [RCT2] T1 ON T0.DocNum = T1.DocNum
WHERE Status='N'
GROUP BY T1.DocEntry, T1.InvType
) T15 ON T11.DocEntry = T15.DocEntry --AND T11.ObjType = T15.InvType
LEFT JOIN [OHEM] T16 ON T0.OwnerCode=T16.empID
LEFT JOIN OUSR T17 ON T16.USERID=T17.UserID
-----T18----
LEFT JOIN (SELECT T2.isIns, T2.IsICT, T3.DocDate as 'FechaEnt', T0.SlpCode as 'Vendedor OV', t2.U_Convenio,T2.U_TIPO_Convenio,
T0.DocEntry, T0.DocNum 'Orden',
T0.DocDate 'Fecha orden',
max(T2.DocDate) as FechaFactAme, max (t3.DocDate) as FechaEntAme
FROM ORDR T0
INNER JOIN INV1 T1 ON T1.BaseEntry=T0.DocEntry AND T1.BaseType='17'
INNER JOIN OINV T2 ON T2.DocEntry=T1.DocEntry
left JOIN odln t3 on t1.TrgetEntry=t3.DocEntry
WHERE T2.CANCELED='N' group by t0.SlpCode, t0.DocEntry, t0.DocNum,
t0.DocDate,t2.U_Convenio,T2.U_TIPO_Convenio, t3.DocDate, T2.isIns, T2.IsICT) T18 on T0.DocEntry=T18.DocEntry
left JOIN ( select t0.DocDate, T0.DocNum, T0.DocEntry
FROM ODLN T0 LEFT JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
LEFT JOIN OINV T2 on T1.BaseEntry=T2.DocEntry and T1.BaseRef=T2.DocNum where t0.CANCELED='N'
) T20 on T11.DocEntry=T20.DocEntry
WHERE
(T0.Canceled ='N')
--AND T11.DocNum IS NOT NULL -- NO MUESTRA LAS OV SIN FACTURAS
--AND T15.PagadoFact IS NOT NULL -- NO MUESTRA LAS OV SIN PAGOS
AND T0.DocTotal = T0.PaidToDate -- MUESTRA LAS OV SOLO PAGOS COMPLETOS
--and T11.CANCELED='N'
AND T0.DocStatus='C'
and t0.DocNum='35759'
Necesito crear el informe en crystal, mi parametro es igual a fechafactame, pero siguiendo ese ejemplo me toma el valor de febrero, cuando debe ser el de marzo, la diferencia esta en que la Orden de Venta tiene 2 facturas de reserva con sus respectivas entregas y una factura de deudor.
Espero que me puedan brindar su apoyo 
