AirNav Systems Forum

AirNav Radar => AirNav Radar Discussion => Topic started by: andrewarles on October 07, 2010, 02:15:07 PM

Title: Yesterday's aircraft count decreasing.
Post by: andrewarles on October 07, 2010, 02:15:07 PM

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

Title: Re: Yesterday's aircraft count decreasing.
Post by: orkney on October 07, 2010, 02:19:36 PM

Hello

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

Andrew

Title: Re: Yesterday's aircraft count decreasing.
Post by: andrewarles on October 07, 2010, 02:28:10 PM

Is it a software bug or is it intentional?

Title: Re: Yesterday's aircraft count decreasing.
Post by: Chris11 on October 07, 2010, 02:29:03 PM

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

Title: Re: Yesterday's aircraft count decreasing.
Post by: andrewarles on October 07, 2010, 02:32:37 PM

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.

Title: Re: Yesterday's aircraft count decreasing.
Post by: AirNav Support on October 07, 2010, 02:32:58 PM

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.

Title: Re: Yesterday's aircraft count decreasing.
Post by: tarbat on October 07, 2010, 03:12:59 PM

Quote from: AirNav Support on October 07, 2010, 02:32:58 PM

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

Title: Re: Yesterday's aircraft count decreasing.
Post by: ACW367 on October 07, 2010, 05:05:47 PM

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.