Desire to be able to produce quick activation statistics and detect new chasers and activators for monthly summaries.
Previously I would have used VB6 to write a code around database. No not this time. Want to stay on MAC so will build database in SQLlite3 as have the files on computer for MLDX and allready using SQL to gather the entries and present in a format suitable for CSV import ,
Current SQL used to extract data from MLDX for import into Sota Database,
select “VK3HRA/p”,strftime(“%d/%m/%Y”,qso_start,’unixepoch’),strftime( “%H%M”,qso_start,’unixepoch’),trim(substr(comments,5,11)),rx_frequency,mode,call,first_name from qso_table_v003 where comments LIKE ‘SOTA%’ and strftime(“%m-%Y”,qso_start,’unixepoch’) = ’02-2013′;
Have been trying to narrow the data to < 2 days without joy. The SQL above gives me monthly data. Just delete the early entries and import. Anyway back to statistics.
Will be using the exported data from SOTA database as source – http://moosedata.com/SOTA/AM_Reports/. Thanks Andy, MM0FMF
My SQL is bit rusty but Google will and the SQLite website is well documented so a quick search directed me to the import command. The data comes as csv so let SQLit3 know that you intend to use a CSV. Create the tables to suit the data then import data into each table.
So need three tables Activator, Chaser and Summit. Will only show activator here as the table are simple string mapping to available data.
sqlite> CREATE TABLE activator (date STRING,activator STRING, summit_code STRING, summit_name STRING, QSO INTEGER);
sqlite> .mode csv
sqlite> .import VK3-activator.csv activator
Check to see it went it,
sqlite> select * from activator;”
Date “,” Activator “,” Summit Code “,”Summit Name “,” QSO “
1-Feb-13,VK3HRA/P,VK3/VT-006,”Mt Saint Phillack”,12
Bingo data !
Chasers who chased VK1 summits,
select * from chaser where summit_code LIKE ‘%VK1%’;
select count(distinct chaser) from chaser where summit_code LIKE ‘%VK1%’;
select distinct activator from activator where date like ‘%Feb-13%’ and activator like ‘%VK1%’;
select count(*) from summit where Summit_code like ‘%VK1%’;
select count(distinct summit_code) from summit where Summit_code like ‘%VK1%’;
Save the CSV file as “Windows Comma Separated” from excel to adresses the CRLF issues.
set .mode to CSV else import will fail with no error message.