Buscador de licitaciones

SQL : WITH consulta_global AS( select top 100 percent *, ROW_NUMBER() OVER( ORDER BY fechapresentacion DESC) AS Row from (SELECT DISTINCT TOP 100 percent i.idinformacion,i.titulo,i.fecha,i.keywords, ida.mostrardatos, ida.categorias, ida.numlicitacion, ida.version, ida.numcontrato, ida.fechapublicacion,ida.fechapresentacion,datediff(minute,ida.fechapublicacion,getdate()) as dif1, (select top 1 ag2.idagenda from agenda ag2, temas t2 where ag2.idcontenido=ag.idcontenido AND ag2.idtema=t2.idtema AND ag2.tipocontenido=11 AND (datediff(second,t2.fechaini,getdate()) is NULL OR datediff(second,t2.fechaini,getdate())>=0) AND (datediff(second,getdate(),t2.fechafin) is NULL OR datediff(second,getdate(),t2.fechafin)>=0) AND (datediff(second,ag2.horainicio,getdate()) is NULL OR datediff(second,ag2.horainicio,getdate())>=0) AND (datediff(second,getdate(),ag2.horafin) is NULL OR datediff(second,getdate(),ag2.horafin)>=0) order by ag2.idtema) as idagenda, (select top 1 ag2.idtema from agenda ag2, temas t2 where ag2.idcontenido=ag.idcontenido AND ag2.idtema=t2.idtema AND ag2.tipocontenido=11 AND (datediff(second,t2.fechaini,getdate()) is NULL OR datediff(second,t2.fechaini,getdate())>=0) AND (datediff(second,getdate(),t2.fechafin) is NULL OR datediff(second,getdate(),t2.fechafin)>=0) AND (datediff(second,ag2.horainicio,getdate()) is NULL OR datediff(second,ag2.horainicio,getdate())>=0) AND (datediff(second,getdate(),ag2.horafin) is NULL OR datediff(second,getdate(),ag2.horafin)>=0) order by ag2.idtema) as idtema, (select top 1 t2.nombre from agenda ag2, temas t2 where ag2.idcontenido=ag.idcontenido AND ag2.idtema=t2.idtema AND ag2.tipocontenido=11 AND (datediff(second,t2.fechaini,getdate()) is NULL OR datediff(second,t2.fechaini,getdate())>=0) AND (datediff(second,getdate(),t2.fechafin) is NULL OR datediff(second,getdate(),t2.fechafin)>=0) AND (datediff(second,ag2.horainicio,getdate()) is NULL OR datediff(second,ag2.horainicio,getdate())>=0) AND (datediff(second,getdate(),ag2.horafin) is NULL OR datediff(second,getdate(),ag2.horafin)>=0) order by ag2.idtema) as nomtema, (select top 1 t2.idpadre from agenda ag2, temas t2 where ag2.idcontenido=ag.idcontenido AND ag2.idtema=t2.idtema AND ag2.tipocontenido=11 AND (datediff(second,t2.fechaini,getdate()) is NULL OR datediff(second,t2.fechaini,getdate())>=0) AND (datediff(second,getdate(),t2.fechafin) is NULL OR datediff(second,getdate(),t2.fechafin)>=0) AND (datediff(second,ag2.horainicio,getdate()) is NULL OR datediff(second,ag2.horainicio,getdate())>=0) AND (datediff(second,getdate(),ag2.horafin) is NULL OR datediff(second,getdate(),ag2.horafin)>=0) order by ag2.idtema) as idpadre, (select top 1 t2.accesibilidad from agenda ag2, temas t2 where ag2.idcontenido=ag.idcontenido AND ag2.idtema=t2.idtema AND ag2.tipocontenido=11 AND (datediff(second,t2.fechaini,getdate()) is NULL OR datediff(second,t2.fechaini,getdate())>=0) AND (datediff(second,getdate(),t2.fechafin) is NULL OR datediff(second,getdate(),t2.fechafin)>=0) AND (datediff(second,ag2.horainicio,getdate()) is NULL OR datediff(second,ag2.horainicio,getdate())>=0) AND (datediff(second,getdate(),ag2.horafin) is NULL OR datediff(second,getdate(),ag2.horafin)>=0) order by ag2.idtema) as accesibilidad FROM informacion i LEFT JOIN agenda ag ON i.idinformacion=ag.idcontenido LEFT JOIN temas t ON ag.idtema = t.idtema LEFT JOIN info_datos_adicionales ida ON i.idinformacion = ida.idinformacion WHERE i.identorno=1 AND ida.licitacion='1' AND ida.estado='adjudicado' AND (ida.fechaadjudicacion>=convert(datetime,'27/07/2023',103) and ida.fechaadjudicacion<=convert(datetime,'27/07/2024 23:59:59',103)) AND (datediff(second,t.fechaini,getdate()) is NULL OR datediff(second,t.fechaini,getdate())>=0) AND (datediff(second,getdate(),t.fechafin) is NULL OR datediff(second,getdate(),t.fechafin)>=0) AND (datediff(second,ag.horainicio,getdate()) is NULL OR datediff(second,ag.horainicio,getdate())>=0) AND (datediff(second,getdate(),ag.horafin) is NULL OR datediff(second,getdate(),ag.horafin)>=0) AND ag.tipocontenido=11 ) as subconsulta) SELECT * FROM consulta_global where (Row BETWEEN 1 AND 61)