-
-
Save anonymous/e530b54e93e07b503164 to your computer and use it in GitHub Desktop.
Dada la siguiente consulta, diga cuando se acaba el mundo.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
vw.categoria_id tipoBoleto_id, | |
MAX(vw.total) total | |
FROM | |
(SELECT | |
piv.corrida_id, | |
piv.feccorrida, | |
piv.numsecorigen, | |
piv.numsecdestino, | |
daf.categoria_id, | |
COUNT(daf.disponibilidad_id) total | |
FROM | |
(SELECT | |
tc.numsecorigen, | |
tc.numsecdestino, | |
tc.corrida_id, | |
tc.feccorrida | |
FROM | |
CORRIDA_TRAMO tcWHERE tc.activo = 1 | |
) piv, | |
(SELECT | |
da.disponibilidad_id, | |
da.secorigen, | |
da.secdestino, | |
da.categoria_id, | |
da.corrida_id, | |
da.feccorrida | |
FROM | |
(SELECT | |
vct.corrida_id, | |
vct.feccorrida, | |
vct.secorigen, | |
vct.secdestino, | |
vct.numasiento, | |
vct.categoria_id, | |
vct.statusasiento, | |
vct.disponibilidad_id | |
FROM | |
(SELECT | |
da.corrida_id, | |
da.feccorrida, | |
da.secorigen, | |
da.secdestino, | |
da.numasiento, | |
da.categoria_id, | |
da.statusasiento, | |
da.disponibilidad_id | |
FROM | |
DISPONIBILIDAD da | |
WHERE | |
da.numasiento IN (SELECT | |
dda.asiento | |
FROM | |
DET_DIAGRAMA_AUTOBUS dda | |
WHERE dda.diagramaautobus_id = :aDiagramaAutobusId | |
AND dda.activo = 1) | |
AND | |
da.activo = 1) vct, | |
(SELECT | |
da2.corrida_id, | |
da2.feccorrida, | |
da2.secorigen, | |
da2.secdestino, | |
da2.numasiento, | |
da2.categoria_id, | |
da2.statusasiento, | |
da2.disponibilidad_id | |
FROM | |
DISPONIBILIDAD da1, | |
DISPONIBILIDAD da2 | |
WHERE | |
da2.corrida_id = da1.corrida_id | |
AND da2.feccorrida = da1.feccorrida | |
AND da2.numasiento = da1.numasiento | |
AND da2.secorigen > da1.secorigen | |
AND da2.secorigen > :aSecOrigen | |
AND da2.secdestino <= :aSecDestino | |
AND da1.categoria_id IS NOT NULL | |
AND da2.categoria_id IS NULL | |
AND da1.activo = 1 | |
AND da2.activo = 1) clv | |
WHERE | |
vct.corrida_id=clv.corrida_id(+) | |
AND vct.feccorrida=clv.feccorrida(+) | |
AND vct.secorigen=clv.secorigen(+) | |
AND vct.secdestino=clv.secdestino(+) | |
AND vct.numasiento=clv.numasiento(+) | |
AND vct.categoria_id=clv.categoria_id(+) | |
AND vct.statusasiento=clv.statusasiento(+) | |
AND vct.disponibilidad_id=clv.disponibilidad_id(+) | |
AND clv.corrida_id IS NULL | |
) da | |
) daf | |
WHERE | |
piv.corrida_id = daf.corrida_id | |
AND | |
piv.feccorrida = daf.feccorrida | |
AND | |
((daf.categoria_id IS NULL AND daf.secorigen <> :aSecOrigen) OR daf.categoria_id IS NOT NULL) | |
AND | |
((daf.secorigen <= piv.numsecorigen AND daf.secdestino > piv.numsecorigen) OR (daf.secorigen >= piv.numsecorigen AND daf.secorigen < piv.numsecdestino)) | |
GROUP BY | |
piv.corrida_id, | |
piv.feccorrida, | |
piv.numsecorigen, | |
piv.numsecdestino, | |
daf.categoria_id | |
ORDER BY | |
daf.categoria_id | |
) vw | |
WHERE | |
vw.numsecorigen >= :aSecOrigen | |
AND | |
vw.numsecdestino <= :aSecDestino | |
AND | |
vw.corrida_id = :aCorridaId | |
AND | |
vw.feccorrida = :fecCorrida | |
AND | |
vw.categoria_id <> 1 | |
GROUP BY | |
vw.categoria_id |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment