How to create a report from a few weeks ago

[Harry]

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

[malc41]

Harry i'm working on something

do you have/use tools like SQLite Maestro or SQLite Database Explorer

[tarbat]

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.

[Spaice]

Tarbat, thanks for the link very useful.  Versions are available for Mac and Linux also.

[malc41]

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.?

[tarbat]

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%")

[malc41]

Thanks Tarbat

It was just getting my head around the distinct issue, but of course the modeS helped.

Many thanks

[tarbat]

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.

[malc41]

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

[tarbat]

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

[malc41]

Great thanks for that , hopefully this will solve some of the problems until we get the inbuilt facility? :-)

[Harry]

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

[malc41]

Have to agree Harry