Yesterday's aircraft count decreasing.

[andrewarles]

Hi,

Could someone explain to me why the aircraft count decreases as you go through the day when you use the Quick Set "Yesterday" in MyLog?

At 12.55 I had 1186 a/c for yesterday
At 13.12 I had 1169 a/c for yesterday
At 16.08 I had 1060 a/c for yesterday

Andrew

[orkney]

Hello

If you receive the same aircraft again today as yesterday the total for yesterday will go down.

Andrew

[andrewarles]

Is it a software bug or is it intentional?

[Chris11]

I have set up the ANRB so that I get an email of the days aircraft every night after midnight

[andrewarles]

Me too Chris, I just thought it was strange that when you're searching for yesterday's a/c it's not actually all of yesterday's a/c.

[AirNav Support]

Its the way the database is recorded.

As the database stores, first and last time recieved. Hence if an aircraft appears today and yesterday the row will store it was seen today and therefore won't appear as yesterday.

[tarbat]

Its the way the database is recorded.

As the database stores, first and last time recieved. Hence if an aircraft appears today and yesterday the row will store it was seen today and therefore won't appear as yesterday.

Unless you change the way the "Yesterday" query works to look at the first and last times in the Flights table, instead of the Aircraft table.  Simples!!

I've posted the SQL to do this before, but here it is again (the important bit is the use of the Flights table in the WHERE statement):

SELECT DISTINCT
  Aircraft.Registration AS "Reg",
  Flights.Callsign AS "Flight ID",
  Flights.Route AS "Route",
  Aircraft.AircraftTypeSmall AS "ICAO Type",
  Aircraft.Airline AS "Airline",
  Aircraft.AircraftTypeLong AS "Aircraft",
  Flights.StartAltitude AS "Start Altitude",
  substr(Flights.StartTime,1,16) AS "Flight Started",
  Flights.EndAltitude AS "End Altitude",
  substr(Flights.EndTime,1,16) AS "Flight Ended",
  Aircraft.ModeS AS "Mode S"
FROM
 Aircraft
 LEFT OUTER JOIN Flights ON (Aircraft.ModeS=Flights.ModeS)
WHERE
  (date('now','-1 day') = substr(Flights.EndTime,1,4)||"-"||substr(Flights.EndTime,6,2)||"-"||substr(Flights.EndTime,9,2))
ORDER BY
  Aircraft.Registration,
  Flights.EndTime,
  Aircraft.ModeS

[ACW367]

Rest assured all the aircraft received yesterday will continue to be annotated on the the table generated on the reporter tab, data generated there will not change.