Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT * FROM ( SELECT x.*, c.flightRange,c.idcitypairs,
- (SELECT idtasks_fmg FROM tasks_fmg WHERE tasks_fmg.rotationId = x.rotationId AND STA BETWEEN DATE_SUB(CAST('2019-12-06' AS DATE), INTERVAL 3 DAY) AND DATE_ADD(CAST('2019-12-06' AS DATE), INTERVAL 31 DAY) ORDER BY idtasks_fmg DESC LIMIT 0, 1) as idtasks_fmg,
- tango.IDTangoHistory IDTangoHistoryInbound,
- tangoOutbound.IDTangoHistory IDTangoHistoryOutbound
- FROM ( SELECT
- LEFT(inbound.atcCallsign, 3) acOwner,
- inbound.acRegistration,
- inbound.dataVersion,
- inbound.flightId fFlightId,
- inbound.rotationId rotationId,
- inbound.rotationSegment fRotationSegment,
- REPLACE(inbound.iataFlightNumber,' ',' ') fFlight,
- inbound.originDestination DEP,
- inbound.stand fStand,
- inbound.scheduledTime STA,
- inbound.internalTime ITA,
- inbound.blockTime ONB,
- inbound.actualTime fAT,
- inbound.acType,
- inbound.acTypeVersion,
- outbound.flightId nFlightId,
- outbound.rotationSegment nRotationSegment,
- REPLACE(outbound.iataFlightNumber,' ',' ') nFlight,
- outbound.originDestination ARR,
- outbound.stand nStand,
- outbound.scheduledTime STD,
- outbound.internalTime ITD,
- outbound.blockTime OFB,
- outbound.actualTime nAT,
- NULL cleanType,
- NULL catType,
- CAST(inbound.scheduledTime AS DATE) fDate,
- CAST(outbound.scheduledTime AS DATE) nDate,
- inbound.idfmg_flightinfos idfmg_flightinfos_inbound,
- outbound.idfmg_flightinfos idfmg_flightinfos_outbound
- FROM
- (((SELECT DISTINCT
- scheduledTime,
- MAX(dataVersion) dataVersion,
- rotationSegment,
- IF(rotationId = 0, flightId, rotationId) rotationId
- FROM
- (SELECT inbound.*
- FROM (SELECT idfmg_flightinfos
- FROM (SELECT c.rotationId, c.rotationSegment, MAX(c.dataVersion) as dataVersion
- FROM(SELECT rotationId, MAX(rotationSegment) as rotationSegment
- FROM fmg_flightinfos_idx_a a
- LEFT JOIN fmg_flightinfos_cx cx ON a.scheduledTimeDate = cx.scheduledTimeDate AND a.flightId = cx.flightId
- WHERE a.scheduledTimeDate >= DATE_SUB('2019-12-06', INTERVAL 3 DAY)
- GROUP BY rotationId) b
- INNER JOIN fmg_flightinfos_idx_a c ON b.rotationId = c.rotationId AND b.rotationSegment = c.rotationSegment
- GROUP BY c.rotationId, c.rotationSegment) as base
- INNER JOIN fmg_flightinfos_idx_a inbound ON base.rotationId = inbound.rotationId AND base.rotationSegment = inbound.rotationSegment AND base.dataVersion = inbound.dataVersion
- WHERE inbound.scheduledTimeDate >= DATE_SUB('2019-12-06', INTERVAL 3 DAY)) as final
- INNER JOIN fmg_flightinfos inbound ON final.idfmg_flightinfos = inbound.idfmg_flightinfos)fmg_flightinfos
- WHERE
- serviceType IN ('LL' , 'CP', 'CA', 'LF','CC','LI','LZ')
- GROUP BY fmg_flightinfos.rotationSegment , fmg_flightinfos.rotationId) F
- LEFT JOIN (SELECT DISTINCT
- scheduledTime,
- MAX(dataVersion) dataVersion,
- rotationSegment,
- IF(rotationId = 0, flightId, rotationId) rotationId
- FROM
- (SELECT outbound.*
- FROM (SELECT idfmg_flightinfos
- FROM (SELECT c.rotationId, c.rotationSegment, MAX(c.dataVersion) as dataVersion
- FROM(SELECT rotationId, MAX(rotationSegment) as rotationSegment
- FROM fmg_flightinfos_idx_d a
- LEFT JOIN fmg_flightinfos_cx cx ON a.scheduledTimeDate = cx.scheduledTimeDate AND a.flightId = cx.flightId
- WHERE a.scheduledTimeDate >= DATE_SUB('2019-12-06', INTERVAL 3 DAY)
- GROUP BY rotationId) b
- INNER JOIN fmg_flightinfos_idx_d c ON b.rotationId = c.rotationId AND b.rotationSegment = c.rotationSegment
- GROUP BY c.rotationId, c.rotationSegment) as base
- INNER JOIN fmg_flightinfos_idx_d outbound ON base.rotationId = outbound.rotationId AND base.rotationSegment = outbound.rotationSegment AND base.dataVersion = outbound.dataVersion
- WHERE outbound.scheduledTimeDate >= DATE_SUB('2019-12-06', INTERVAL 3 DAY)) as final
- INNER JOIN fmg_flightinfos outbound ON final.idfmg_flightinfos = outbound.idfmg_flightinfos)fmg_flightinfos
- WHERE
- serviceType IN ('LL' , 'CP', 'CA', 'LF','CC','LI','LZ')
- GROUP BY fmg_flightinfos.rotationSegment , fmg_flightinfos.rotationId) N ON (F.rotationSegment < N.rotationSegment)
- AND (F.rotationId = N.rotationId))
- INNER JOIN fmg_flightinfos inbound ON F.dataVersion = inbound.dataVersion
- AND F.rotationSegment = inbound.rotationSegment
- AND F.scheduledTime = inbound.scheduledTime
- AND F.rotationId = inbound.rotationId)
- LEFT JOIN
- fmg_flightinfos outbound ON N.dataVersion = outbound.dataVersion
- AND N.rotationSegment = outbound.rotationSegment
- AND IF(outbound.actualTime > '0000-00-00 00:00:00',outbound.actualTime,IF(outbound.blockTime > '0000-00-00 00:00:00',outbound.blockTime,IF(outbound.internalTime > '0000-00-00 00:00:00',outbound.internalTime,outbound.scheduledTime))) >
- IF(inbound.actualTime > '0000-00-00 00:00:00',inbound.actualTime,IF(inbound.blockTime > '0000-00-00 00:00:00',inbound.blockTime,IF(inbound.internalTime > '0000-00-00 00:00:00',inbound.internalTime,inbound.scheduledTime)))
- AND N.rotationId = outbound.rotationId
- WHERE
- ( CAST(inbound.scheduledTime AS DATE) >= '2019-12-06' OR CAST(outbound.scheduledTime AS DATE) >= '2019-12-06')
- ORDER BY STA)x
- LEFT JOIN cgs02.airports dep ON x.DEP = dep.iata
- LEFT JOIN cgs02.airports arr ON(x.ARR = arr.iata)
- LEFT JOIN cgs02.citypairs c ON dep.idairports = c.depidairports AND arr.idairports = c.arridairports
- LEFT JOIN (SELECT AirlineDesignator, FlightNumber, ScheduledTimeOfArrival, AircraftRegistration, IDTangoHistory FROM cgs02.tango_history_cgs ORDER BY IDTangoHistory DESC LIMIT 0, 100000) tango
- ON CONCAT(tango.AirlineDesignator, ' ', tango.FlightNumber) = REPLACE(x.fFlight,' ',' ') AND CAST(tango.ScheduledTimeOfArrival AS DATE) = CAST(x.STA AS DATE) AND tango.AircraftRegistration = x.acRegistration
- LEFT JOIN (SELECT AirlineDesignator, FlightNumber, ScheduledTimeOfDeparture, AircraftRegistration, IDTangoHistory FROM cgs02.tango_history_cgs ORDER BY IDTangoHistory DESC LIMIT 0 , 100000) tangoOutbound
- ON CONCAT(tangoOutbound.AirlineDesignator, ' ', tangoOutbound.FlightNumber) = REPLACE(x.nFlight,' ',' ') AND CAST(tangoOutbound.ScheduledTimeOfDeparture AS DATE) = CAST(x.STD AS DATE) AND tangoOutbound.AircraftRegistration = x.acRegistration
- ) BULLSHIT ORDER BY idfmg_flightinfos_inbound,idfmg_flightinfos_outbound
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement