Skip to Content
0
Former Member
Aug 20, 2015 at 03:22 PM

Agregar última fecha actividad en Query

110 Views

Estimados, Estoy haciendo una query para las oportunidades y tengo un problema. Necesito agregar solamente la última etapa y la fecha pero se me duplica la oportunidad según las etapas que tenga. (Ver imágenes)

Hice esta pequeña query solo para graficar mi problema. La real la adjuntaré al final de este post.

Necesito ideas para que solo me aparezca la ultima etapa con su respectiva Fecha.

Saludos.

SELECT t0.opprid, t0.cardcode, t0.cardname, t0.maxsumloc, t2.descript, t1.opendate

From oopr t0

inner join opr1 t1 on t0.opprid = t1.opprId

inner join oost t2 on t1.step_id = t2.num

Esta es la Query!

---------------------------------------------------------------------------------------------------

SELECT T0.OpprId 'Numero de oportunidad', T0.Name 'Nombre Oportunidad', t0.cardcode 'Rut', T0.CardName 'Cliente', T1.SlpName 'Vendedor',CASE T0.intRate when '1' then 'Verde' when '2' then 'Amarillo' when '3' then 'Rojo' END 'Avance', T0.OpenDate 'Fecha de inicio', T0.CloseDate 'Fecha de cierre', T0.PredDate 'Fecha de cierre prevista', T0.MaxSumLoc 'Monto Potencial', T0.Cloprcnt '% de cierre', CONVERT(INT,T0.MaxSumLoc*T0.Cloprcnt/100) 'Importe Ponderado', CASE T0.Status WHEN 'O' THEN 'Abierta' WHEN 'W' THEN 'Ganada' WHEN 'L' THEN 'Perdida' END 'Estado',

T3.Descript 'Ultima Etapa', T3.SlpName 'Empleado de Venta ultima etapa',

ISNULL(T5.Descript,'Ganada') 'Razón de Pérdida', T10.Descript 'Descripcion',

T7.Descript 'Fuente informacion', T8.IndName 'Ramo',T6.nAct 'Actividades', T6.lastDte 'Fecha ultima actividad' ,T0.U_ID_MP '# ID MP', T0.U_F_C_MP 'Fecha cierre licitacion MP', T0.U_H_C_MP 'Hora cierre licitacón', T0.U_F_A_MP 'Fecha adjudicacion MP', T0.U_Dur_Con 'Duracion contrato (meses)', CASE T0.U_T_Gar WHEN 1 THEN 'No requiere' WHEN 2 THEN 'Boleta garantia' WHEN 3 THEN 'Poliza garantia' END 'Tipo de garantia', T0.U_Monto_Gar 'Monto garantia', T0.U_Fecha_Gar 'Fecha entrega garantia',

T9.Name 'Competidor', T9.Amenaza 'Nivel de Amenaza', T9.Memo 'Cantidad', 'Alcamed' 'Empresa'

FROM OOPR T0

INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode

INNER JOIN (SELECT t1.OpprId, t3.Descript, t5.SlpName FROM OPR1 t1 INNER JOIN

(SELECT OpprId, MAX(line) AS Linea FROM opr1 GROUP BY opprid) t2 ON (t1.OpprId = t2.OpprId AND t1.Line = t2.Linea)

INNER JOIN (SELECT opprid, Line, SlpCode FROM opr1) t4 ON (t2.OpprId = t4.opprid AND t2.Linea = t4.Line)

INNER JOIN OOST t3 ON t1.Step_Id = t3.StepId

INNER JOIN OSLP t5 ON T4.SlpCode = t5.SlpCode) T3 ON T0.OpprId = T3.OpprId

LEFT JOIN (SELECT t0.OpportId, t2.Descript FROM (SELECT OpportId, MIN(Line) AS Linea FROM opr5 GROUP BY OpportId) t0

INNER JOIN OPR5 t1 ON (t0.OpportId = t1.OpportId AND t0.Linea = t1.Line)

INNER JOIN OOFR t2 ON t1.ReasondId = t2.Num) T5 ON T0.OpprId = T5.OpportId

LEFT JOIN (SELECT ParentID, MAX(CntctDate) AS lastDte, COUNT(*) AS nAct FROM oclg GROUP BY ParentID) T6 ON T0.OpprId = T6.ParentID

LEFT JOIN OOSR T7 ON T0.Source = T7.Num

LEFT JOIN OOND T8 ON T0.Industry = T8.IndCode

LEFT JOIN (SELECT t0.Opportid, t1.Memo, t2.Name, CASE t1.ThreatLevl WHEN 1 THEN 'Baja' WHEN 2 THEN 'Media' WHEN 3 THEN 'Alta' END AS Amenaza

FROM (Select OpportId, MIN(Line) AS Linea from opr3 GROUP BY OpportId) t0 INNER JOIN opr3 t1 ON (t0.opportid = t1.opportid AND t0.linea = t1.line)

INNER JOIN OCMT t2 on t1.competid = t2.competid) T9 ON T0.OpprId = T9.OpportId

LEFT JOIN (SELECT t0.OprId, T2.Descript FROM (SELECT OprId, MIN(Line) AS Linea FROM OPR4 GROUP BY OprId) t0

INNER JOIN OPR4 t1 ON t0.OprId = t1.OprId

INNER JOIN OOIN t2 ON t1.IntId = t2.Num) T10 ON T0.Opprid = T10.OprId

INNER JOIN OCRD X ON T0.cardcode = x.cardcode

ORDER BY T0.OpenDate

Attachments

Query Prueba.jpg (85.5 kB)