anything
AirNav RadarBox
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
 


Author Topic: Hourly count of Aircraft  (Read 2864 times)

0 Members and 1 Guest are viewing this topic.

lennox2604

  • RadarBox Beta Testers
  • New Member
  • *
  • Posts: 32
Hourly count of Aircraft
« on: October 18, 2009, 04:43:19 PM »
Hi

Does anybody have a SQL script which will extract the hourly count of flights.
 I have tried to get one up and running but to no avail.

The Mylog only gives for last hour

I have a cunning plan to use the information but first i need to extract it

Any help or script welcome



 

Aerotower

  • Hero Member
  • *****
  • Posts: 525
Re: Hourly count of Aircraft
« Reply #1 on: October 18, 2009, 05:23:21 PM »
See this attachment.

lennox2604

  • RadarBox Beta Testers
  • New Member
  • *
  • Posts: 32
Re: Hourly count of Aircraft
« Reply #2 on: October 18, 2009, 06:26:40 PM »
Missed the point here

What it does NOT do is tell you the number 0000-0100; 0100-0200 thus
18/10/09  0000-0100  4
18/10/09  0100-0200  6

It is just needs extracted from the Mylog not just last 10 minutes and last hour

and i cannot get a SQL statement to do it



Aerotower

  • Hero Member
  • *****
  • Posts: 525
Re: Hourly count of Aircraft
« Reply #3 on: October 19, 2009, 01:17:19 AM »
had not noticed it. It is not possible to change the time to see the records per hour.
Cumps

tarbat

  • ShipTrax Beta Testers
  • Hero Member
  • *
  • Posts: 4219
    • Radarbox at Easter Ross
Re: Hourly count of Aircraft
« Reply #4 on: October 19, 2009, 07:32:26 AM »
SELECT
  substr(Flights.EndTime,1,13) AS Date_Hour,
  COUNT(Flights.ModeS) AS Count_of_Flights
FROM
 Flights
GROUP BY
  Date_Hour
ORDER BY
  Date_Hour


Gives something like this:



Or you could use the same SQL query on the v_Flights view to get earlier dates.
« Last Edit: October 19, 2009, 09:12:41 AM by tarbat »

Terre

  • Jr. Member
  • **
  • Posts: 86
Re: Hourly count of Aircraft
« Reply #5 on: October 19, 2009, 08:33:58 PM »
Tarbat, a fine script you've made there. I had the idea to handle it with a pivot table but failed as it seems that certain sql commands need to be 'rewritten' to work in sqlite. By far your script is more elegant - Salute!