GROUPRES

incluir columnas de plazo en meses de entrada (fecha max entrada vs fecha contrato) y plazo de saldo (fecha max de hip vs fecha de contrato)

select
cp.value as contrato,
bp.name as cliente,
datecontract::date as fecha_contrato,
ec.name as estado,
tb.name as inmueble,
cp.plannedamt as valor,
au.name as ejecutivo,
foportunidad::date as fecha_oportunidad,
med.name as medio,
can.name as canal,
extract(year from (age (datecontract,foportunidad)))*12+
extract(month from (age (datecontract,foportunidad))) as meses
from c_project cp
inner join tb_estadocontrato ec on ec.tb_estadocontrato_id = cp.tb_estadocontrato_id
inner join c_bpartner bp on bp.c_bpartner_id = cp.c_bpartner_id
inner join C_ProjectLine pl on pl.c_project_id = cp.c_project_id
inner join m_product mp on mp.m_product_id = pl.m_product_id and mp.isactive = 'Y' and bieninmueble = 'Y'
inner join tb_tipobien tb on tb.tb_tipoBien_ID = mp.tb_tipoBien_ID
inner join ad_user au on au.ad_user_id = cp.salesrep_id
left join c_order cot on cot.c_order_id = cp.c_order_id
left join c_opportunity op on op.c_opportunity_id = cot.c_opportunity_id
left join c_channel can on can.c_channel_id = op.c_channel_id
left join C_InformationMedia med on med.C_InformationMedia_ID = op.C_InformationMedia_ID
where cp.ad_client_id = 1000005
and ec.isvalid = 'Y'

se cambio de nombre