An SQL query for unique SKCC numbers in CQRlog

Posted by Nate Bargmann on Mon, Aug 15, 2011

As a follow-on to the MySQL server spawned by CQRlog article, I present a nice bit of SQL for your amusement.  This query is based on the storage of SKCC numbers being in the award column and in the format of SKCC #;.  This query works in either CQRlog’s SQL console or the mysql utility:

SELECT DISTINCT qsodate, time_off, callsign, freq, mode, name, rst_r, rst_s, award FROM cqrlog_main WHERE award LIKE "SKCC%" AND mode LIKE "CW" GROUP BY award ORDER BY qsodate, time_off;
  • The use of GROUP BY award weeds out duplicate numbers that have a different callsign, such as for K3Y events and such.
  • The SQL console of CQRlog only supports CSV and HTML output formats.  Most likely some sort of Perl script will be needed to generate ADIF, hence why I was anxious to exploit independent database access.

Comments

Comment by ok2cqr on 2011-09-16 11:49:19 -0500

Hi Nate,

you don’t have to use SQL for this. You can do that also directly in CQRLOG.

Go to QSO list window -> Statistics -> Membership tracking -> Rebuild membership statistics. Check Rebuild options for SKCC club and click to Start. Now all QSO with SKCC members will have the SKCC number in Award field. Now we are ready to filter QSO. Go to Filter -> Create. Set mode to CW, in Club combobox select e.g. Club 1 (if you have SKCC as first club), in Membership combobox choose SKCC club. Click to OK.

Now you should see all QSO with SKCC members, no dupes.

If you want to do some changes in sql query created by filter, you can use SQL console. Go to Filter -> SQL console. In SQL console window click to the first icon from the right. It adds SQL query created by filter to the window and you can do changes.

73 Petr

Comment by Nate Bargmann on 2011-09-16 12:04:11 -0500

Thanks Petr.

I’m learning more about CQRlog all the time and it is a fine program. I do like playing with SQL so that is also part of my motivation of my post, and also so I won’t forget how I did that!

Thanks for your informative reply.

Comment by Nate Bargmann on 2011-09-17 13:56:22 -0500

I did find a kink in using the filter method and that is not a fault of CQRlog. The SKCC membership database consists of the callsign and the SKCC number. I found a few QSOs were missing using the filter method as the membership file has not been updated upstream. That’s not a fault of CQRlog but of the SKCC member who has not had SKCC update his call in the file.

So it goes…