An SQL query for unique SKCC numbers in CQRlog

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.

 

About Nate Bargmann

An amateur radio operator, vintage motorcycle enthusiast, and all around tinkerer interested in too many things to focus on one for very long. When I'm typing here, it's likely that I should be doing something else.
This entry was posted in Amateur Radio, Software. Bookmark the permalink.

3 Responses to An SQL query for unique SKCC numbers in CQRlog

  1. ok2cqr says:

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

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

  3. 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…

Leave a Reply