Hi,
I want to make some report from some weeks ago, but via the Reporter tool I only can choose between today and yesterday. Who can help me?
Harry
Harry i'm working on something
do you have/use tools like SQLite Maestro or SQLite Database Explorer
If the aircraft had flight IDs, or you were running the v3.0 beta at the time, then this SQL should work (example is for 23rd June):
SELECT *
FROM
v_Flights
WHERE
(v_Flights.EndTime LIKE "2009/06/23%") OR
(v_Flights.StartTime LIKE "2009/06/23%")
Download SQLite Database Browser from http://sqlitebrowser.sourceforge.net/
and copy/paste this SQL into it.
Tarbat, thanks for the link very useful. Versions are available for Mac and Linux also.
tarbat
I was trying to get it to do similar but my problem was multiple instances of the same registration, which I was trying to avoid, any ideas.?
SELECT DISTINCT
v_Flights.ModeS,
v_Flights.Registration
FROM
v_Flights
WHERE
(v_Flights.EndTime LIKE "2009/06/23%") OR
(v_Flights.StartTime LIKE "2009/06/23%")
Thanks Tarbat
It was just getting my head around the distinct issue, but of course the modeS helped.
Many thanks
The DISTINCT bit will ensure you don't get duplicates of the fields you've chosen to output. So, for example, if you then added v_Flights.Callsign, you would get a lot more records, one for each callsign used by the aircraft that day. Basically, the more fields you output, the more records you will get. If you want very few duplicates, only output the fields you need.
What I ws trying to do was stop the duplicates but also be able to display other columns so as to see the information etc.
this is what I came up with:
SELECT registration,starttime
FROM
flightsold
WHERE
(flightsold.EndTime LIKE "2008/03/13%") OR
(flightsold.StartTime LIKE "2008/03/13%")
group by registration
You could experiment with the aggregation functions. This query just puts out one record per aircraft, and show the latest start date/time.
SELECT DISTINCT
v_Flights.ModeS,
v_Flights.Registration,
MAX(v_Flights.StartTime) AS FIELD_1
FROM
v_Flights
WHERE
(v_Flights.EndTime LIKE "2009/06/27%")
GROUP BY
v_Flights.ModeS,
v_Flights.Registration
ORDER BY
v_Flights.Registration
More help on Aggregate Functions at http://www.sqlite.org/lang_aggfunc.html
Great thanks for that , hopefully this will solve some of the problems until we get the inbuilt facility? :-)
Quote from: malc41 on July 02, 2009, 03:12:05 PM
Great thanks for that , hopefully this will solve some of the problems until we get the inbuilt facility? :-)
I hope this will become a function soon, because i find it a bit strang to get the data from an external tool.
harry
Have to agree Harry