How best to filter for a New Band Country competition?

3 posts / 0 new
Last post
KX4QC
KX4QC's picture
How best to filter for a New Band Country competition?

Yes, CQRLOG does this, but at preset I' using either an ADIF export to spreadsheet or my clumsy SQL.

Here's the problem: Our DX club has an annual competition to see who has the most "New Band Countries" for the year.

A "New Band Country" must be:

A new DXCC entity contact this year, on a new Band.

but NOT a DXCC entity logged on this Band before the contest year.

This is a filtering job that is done by lesser logging apps, so I feel sure that there has to be a simple way of doing it with CQRLOG.

How best to do?

73, John KX4QC

oh1kh
How best to filter for a New Band Country competition?

Hi John!

Could it be something like:

 select distinct(dxcc_ref.pref) as A from cqrlog_common.dxcc_ref inner join cqrlog003.cqrlog_main on dxcc_ref.adif=cqrlog003.cqrlog_main.adif   where cqrlog003.cqrlog_main.qsodate between '2022-01-01' and '2022-12-31' and cqrlog003.cqrlog_main.band='15M' order by A asc;

+--------+

| A      |

+--------+

| 4X     |

| 5B     |

| 9A     |

| BY     |

| DL     |

| E7     |

| EA     |

| F      |

| FH     |

| FJ     |

| G      |

| HA     |

| HB     |

| I      |

| JA     |

| LZ     |

| OE     |

| OH     |

| OK     |

| OM     |

| S5     |

| SV     |

| TA     |

| UA(AS) |

| UN     |

| VE     |

| VU     |

| W      |

| XE     |

| YU     |

+--------+

30 rows in set (0.006 sec)

 

 

You have to change the log number where I have 003 and the band where I have 15M I have not confirmed that it works. Perhaps you can compare with your spreadsheet results from last year (just change 2022 to 2021)

--
Saku
OH1KH

oh1kh
How best to filter for a New Band Country competition?

Hi
After read again your message I think you need to run the Sql twice.
First with dates 1900-01-01 and 2021-12-31 and after it with dates 2022-01-01 and 2022-12-31. Then compare results to see only new ones worked this year.
And do same for all bands.
Of course those two runs could be combined to same sql sentence, but it makes more work.

And you could also put all different band sentences to same bash script and call Mysql from there to get all in one go.
While Cqrlog is running you can access log databases from command line mysql either via localhost port 64000 or via socket file ~/.config/cqrlog/database/sock

--
Saku
OH1KH