SELECT v.idvrem as rec_idrec, v.idruta as idruta, r.nombre as rutanombre, v.idpromo as idpromo, 0 as idperiodo, (v.cantpagos+1) as anual, v.numpagos as sem, v.idvrem as idvrem, v.fecha as fecha, v.perpago as perpago, v.numpagos as numpagos, (select coalesce(max(ka.fecha),'') from .vremabo ka where ka.idvrem=v.idvrem) as ultimoabono, v.total as total, v.aboper as aboper, v.impcred as impcred, v.cargos as cargos, v.abonos as abonos, v.saldo as saldo, v.impvenc as impvenc, (CASE WHEN a.campocb=0 THEN v.idvrem WHEN a.campocb=1 THEN c.rfc WHEN a.campocb=2 THEN c.identif WHEN a.campocb=3 THEN c.numexpediente ELSE c.folio END) as rec_cb, v.proxvisita rec_fecha, 0 as rec_pago, 0 as rec_pagopena, 0 as rec_adestiempo, '' as rec_motivonopago, c.idperson as idperson, c.nombre as nombre, c.callenums as callenums, c.col as col, c.mpo as mpo, c.edo as edo, c.cp as cp, c.telcel as telcel, c.tel as tel, c.email as email, v.diavisita as diavisita, v.idaval1 as a1_idperson, coalesce(a1.nombre,'') as a1_nombre, coalesce(a1.callenums,'') as a1_callenums, coalesce(a1.col,'') as a1_col, coalesce(a1.mpo,'') as a1_mpo, coalesce(a1.edo,'') as a1_edo, coalesce(a1.cp,'') as a1_cp, coalesce(a1.telcel,'') as a1_telcel, coalesce(a1.tel,'') as a1_tel, coalesce(a1.email,'') as a1_email, v.idaval2 as a2_idperson, coalesce(a2.nombre,'') as a2_nombre, coalesce(a2.callenums,'') as a2_callenums, coalesce(a2.col,'') as a2_col, coalesce(a2.mpo,'') as a2_mpo, coalesce(a2.edo,'') as a2_edo, coalesce(a2.cp,'') as a2_cp, coalesce(a2.telcel,'') as a2_telcel, coalesce(a2.tel,'') as a2_tel, coalesce(a2.email,'') as a2_email, v.latitude as latitud, v.longitude as longitud, 0 as upddatos, 0 as updpago, 0 as updubica, v.rangovenc as rangovenc, c.calif as calif, v.refart as refart, v.cpdtes as cpdtes, FORMAT( COALESCE(100-(v.numvenc*100/v.numpagos) , 0) , 2) as rank, v.vencida as vencida, v.vence as vence, prm.nombre as promonombre, v.idalm as idalm, v.folio as folio, v.foliodcto as foliodcto, v.ordenvisita as ordenvisita, v.diasvenc as diasvenc, p.renuevacon as renuevacon, v.cantpagos as cantpagos, v.numvenc as numvenc, (v.numpagos-v.cantpagos) as restantes FROM .vrem v LEFT JOIN .person c ON v.idcte=c.idperson LEFT JOIN .person a1 ON v.idaval1=a1.idperson LEFT JOIN .person a2 ON v.idaval2=a2.idperson LEFT JOIN .ruta as r ON v.idruta=r.idruta LEFT JOIN .person prm ON v.idpromo=prm.idperson LEFT JOIN .alm a ON v.idalm=a.idalm LEFT JOIN .planc p ON v.idplanc=p.idplanc where (r.llave='' or ''='') and (v.saldo>0) and (statdcto=3) and (r.idruta not in (select ru1.idruta from sec_users_ruta ru1 where ru1.idruta=r.idruta) OR v.idruta in (select ru2.idruta from sec_users_ruta ru2 where ru2.login=r.usuariopago))