EA5JTT: WSPR & DXCC

Esta es la lista oficial de territorios para ARRL (Marzo de 2022), si lo que ahora queremos es conocer cuantos países DXCC hemos alcanzado con nuestro WSPR TX, podemos lanzar una consulta SQL a la B.D. WSPRnet que tenemos disponible en WSPR Rocks (https://wspr.rocks/)

Un ejemplo es el que copio a continuación y puede ser facilmente modificado, pero no ampliado pues el límite de entradas de la clausula CASE está en unas 100, pero puede hacer dos consultas, recuerde cambiar el indicativo y las fechas a las que usted prefiera (ATENCIÓN esta primera entrada es la mas actualizada el resto de ejemplos tienen un CASE anterior):

En cuantos territorios ARRL DXCC se ha recibido nuestra señal WSPR (No está completo, el limite de entadas está en unas 100):

SELECT
CASE
WHEN LEFT(rx_sign, 2) = '3A' THEN 'Monaco'
WHEN LEFT(rx_sign, 2) = '4M' THEN 'Venezuela'
WHEN LEFT(rx_sign, 2) = '4V' THEN 'Haiti'
WHEN LEFT(rx_sign, 2) IN ('4X','4Z') THEN 'Israel'
WHEN LEFT(rx_sign, 2) IN ('5B', 'C4', 'P3') THEN 'Chipre'
WHEN LEFT(rx_sign, 2) = '5P' THEN 'Dinamarca'
WHEN LEFT(rx_sign, 2) = '9A' THEN 'Croacia'
WHEN LEFT(rx_sign, 2) = '9H' THEN 'Malta'
WHEN LEFT(rx_sign, 2) = 'C3' THEN 'Andorra'
WHEN LEFT(rx_sign, 2) IN ('BU','BV','BW','BX') THEN 'Taiwan'
WHEN LEFT(rx_sign, 1) = 'B' THEN 'China'
WHEN LEFT(rx_sign, 2) IN ('CA', 'CB', 'CC', 'CD', 'CE') THEN 'Chile'
WHEN LEFT(rx_sign, 2) = 'CN' THEN 'Marruecos'
WHEN LEFT(rx_sign, 2) IN ('CS','CT') THEN 'Portugal'
WHEN LEFT(rx_sign, 2) IN ('DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ','DR') THEN 'Alemania'
WHEN LEFT(rx_sign, 2) IN ('DU','DV','DW','DX','DY','DZ','4D','4E','4F','4G','4H','4I') THEN 'Filipinas'
WHEN LEFT(rx_sign, 3) IN ('EA6', 'EB6', 'EC6', 'ED6', 'EE6', 'EF6', 'EG6','EH6') THEN 'España - Baleares'
WHEN LEFT(rx_sign, 3) IN ('EA8', 'EB8', 'EC8', 'ED8', 'EE8', 'EF8', 'EG8','EH8') THEN 'España - Canarias'
WHEN LEFT(rx_sign, 3) IN ('EA9', 'EB9', 'EC9', 'ED9', 'EE9', 'EF9', 'EG9','EH9') THEN 'España - Ceuta/Melilla'
WHEN LEFT(rx_sign, 2) IN ('EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG','EH') THEN 'España - Península'
WHEN LEFT(rx_sign, 2) = 'E5' THEN 'Islas Cook'
WHEN LEFT(rx_sign, 2) = 'E7' THEN 'Bosnia-Herzegovina'
WHEN LEFT(rx_sign, 2) = 'EW' THEN 'Bielorusia'
WHEN LEFT(rx_sign, 2) IN ('EI', 'EJ') THEN 'Irlanda'
WHEN LEFT(rx_sign, 2) = 'ES' THEN 'Estonia'
WHEN LEFT(rx_sign, 1) = 'F' THEN 'Francia'
WHEN LEFT(rx_sign, 2) IN ('GB', 'GX', 'MX','2E') THEN 'Inglaterra'
WHEN LEFT(rx_sign, 2) IN ('GI', 'GN', 'MI','2I') THEN 'Irlanda del Norte'
WHEN LEFT(rx_sign, 2) IN ('GJ', 'GH', 'MJ','2J') THEN 'Isla Jersey'
WHEN LEFT(rx_sign, 2) IN ('GM', 'GS', 'MM','2M') THEN 'Escocia'
WHEN LEFT(rx_sign, 2) IN ('GU', 'GP', 'MU','2U') THEN 'Guersey'
WHEN LEFT(rx_sign, 2) IN ('GW', 'GC', 'MW','2W') THEN 'Gales'
WHEN LEFT(rx_sign, 2) IN ('GD', 'GT', 'MD','2D') THEN 'Isla de Man'
WHEN LEFT(rx_sign, 1) = 'G' AND SUBSTRING(rx_sign, 2, 1) REGEXP '[0-9]' THEN 'Inglaterra'
WHEN LEFT(rx_sign, 2) IN ('HA', 'HG') THEN 'Hungria'
WHEN LEFT(rx_sign, 2) IN ('HC', 'HD') THEN 'Ecuador'
WHEN LEFT(rx_sign, 2) IN ('HJ', 'HK') THEN 'Colombia'
WHEN LEFT(rx_sign, 2) IN ('HO', 'HP') THEN 'Panama'
WHEN LEFT(rx_sign, 2) IN ('HQ', 'HR') THEN 'Honduras'
WHEN LEFT(rx_sign, 2) = 'HB' THEN 'Suiza'
WHEN LEFT(rx_sign, 2) = 'HH' THEN 'Haiti'
WHEN LEFT(rx_sign, 2) = 'HI' THEN 'Republica Dominicana'
WHEN LEFT(rx_sign, 2) = 'HL' THEN 'Republica Corea'
WHEN LEFT(rx_sign, 3) IN ('IS0', 'IM0') THEN 'Cerdeña'
WHEN LEFT(rx_sign, 1) = 'I' THEN 'Italia'
WHEN LEFT(rx_sign, 2) IN ('LA', 'LB','LC', 'LD', 'LE', 'LF', 'LG', 'LH', 'LI', 'LJ', 'LK', 'LL', 'LM', 'LN') THEN 'Noruega'
WHEN LEFT(rx_sign, 2) IN ('JA','JB','JC','JD','JE','JF','JG','JH','JI','JJ','JK','JL','JM','JN','JO','JP','JQ','JR','JS','7J','7K','7L','7N') THEN 'Japon'
WHEN LEFT(rx_sign, 2) = 'LX' THEN 'Luxemburgo'
WHEN LEFT(rx_sign, 2) = 'LY' THEN 'Lituania'
WHEN LEFT(rx_sign, 2) = 'LZ' THEN 'Bulgaria'
WHEN LEFT(rx_sign, 1) = 'M' AND SUBSTRING(rx_sign, 2, 1) REGEXP '[0-9]' THEN 'Inglaterra'
WHEN LEFT(rx_sign, 2) = 'OE' THEN 'Austria'
WHEN LEFT(rx_sign, 2) IN ('OK', 'OL') THEN 'Chequia'
WHEN LEFT(rx_sign, 2) IN ('OF', 'OG', 'OH', 'OI') THEN 'Finlandia'
WHEN LEFT(rx_sign, 2) = 'OM' THEN 'Eslovaquia'
WHEN LEFT(rx_sign, 2) IN ('ON', 'OO', 'OP','OQ', 'OR', 'OS','OT') THEN 'Belgica'
WHEN LEFT(rx_sign, 2) IN ('OU', 'OV', 'OW','OZ') THEN 'Dinamarca'
WHEN LEFT(rx_sign, 2) IN ('PA', 'PB', 'PC','PD', 'PE', 'PF', 'PG', 'PH', 'PI') THEN 'Paises Bajos'
WHEN LEFT(rx_sign, 2) IN ('PP','PQ','PR','PS','PT','PU','PV','PW','PX','PY','ZV','ZW','ZX','ZY','ZZ') THEN 'Brasil'
WHEN LEFT(rx_sign, 2) = 'PZ' THEN 'Surinam'
WHEN LEFT(rx_sign, 2) IN ('R1','R3','R4','R5','R6','R7') THEN 'Rusia Europea'
WHEN LEFT(rx_sign, 2) = 'R2' THEN 'Kaliningrado'
WHEN LEFT(rx_sign, 2) IN ('R8','R9','R0') THEN 'Rusia Asiática'
WHEN rx_sign REGEXP '^(U[A-I]|R[A-Z])[1,3-7]' THEN 'Rusia Europea'
WHEN LEFT(rx_sign, 3) IN ('UA2', 'RA2') THEN 'Kaliningrado'
WHEN rx_sign REGEXP '^(U[A-I]|R[A-Z])[089]' THEN 'Rusia Asiática'
WHEN LEFT(rx_sign, 3) IN ('UA2', 'RA2') THEN 'Kaliningrado'
WHEN LEFT(rx_sign, 2) IN ('UN','UM','UO','UP','UQ') THEN 'Kazaskan'
WHEN LEFT(rx_sign, 2) = 'S5' THEN 'Eslovenia'
WHEN LEFT(rx_sign, 2) IN ('SA','SB','SC', 'SD', 'SE', 'SF', 'SG', 'SH', 'SI', 'SJ', 'SK', 'SL', 'SM', '7S','8S') THEN 'Suecia'
WHEN LEFT(rx_sign, 2) IN ('SN', 'SO', 'SP','SQ', 'SR') THEN 'Polonia'
WHEN LEFT(rx_sign, 2) IN ('SV', 'SW', 'SX','SY', 'SZ', 'J4') THEN 'Grecia'
WHEN LEFT(rx_sign, 2) IN ('TA', 'TB', 'TC') THEN 'Turquia'
WHEN LEFT(rx_sign, 2) IN ('TI', 'TE') THEN 'Costa Rica'
WHEN LEFT(rx_sign, 2) = 'TF' THEN 'Islandia'
WHEN LEFT(rx_sign, 2) = 'TK' THEN 'Corcega'
WHEN LEFT(rx_sign, 2) IN ('UR','US', 'UT', 'UU', 'UV', 'UW', 'UX', 'UY', 'UZ') THEN 'Ucrania'
WHEN LEFT(rx_sign, 2) = 'V5' THEN 'Namibia'
WHEN LEFT(rx_sign, 2) IN ('VA', 'VB', 'VC', 'VD', 'VE', 'VF', 'VG','VO','VP','VQ','VR','VS','VT','VU','VV','VX','VY') THEN 'Canada'
WHEN LEFT(rx_sign, 2) IN ('VK', 'AX') THEN 'Australia'
WHEN LEFT(rx_sign, 1) IN ('K', 'N', 'W') THEN 'Estados Unidos'
WHEN LEFT(rx_sign, 2) IN ('AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI' , 'AJ', 'AK') THEN 'Estados Unidos'
WHEN LEFT(rx_sign, 2) = 'YL' THEN 'Letonia'
WHEN LEFT(rx_sign, 2) = 'YM' THEN 'Turquia'
WHEN LEFT(rx_sign, 2) IN ('YO', 'YP', 'YQ', 'YR') THEN 'Rumania'
WHEN LEFT(rx_sign, 2) IN ('YT', 'YU') THEN 'Serbia'
WHEN LEFT(rx_sign, 2) = 'ZA' THEN 'Albania'
WHEN LEFT(rx_sign, 3) = 'ZB2' THEN 'Gibraltar'
WHEN LEFT(rx_sign, 3) = 'ZD7' THEN 'St. Helena'
WHEN LEFT(rx_sign, 2) = 'ZF' THEN 'Caiman'
WHEN LEFT(rx_sign, 3) = 'ZL7' THEN ' Chatham '
WHEN LEFT(rx_sign, 3) = 'ZL8' THEN 'Kermadec'
WHEN LEFT(rx_sign, 3) = 'ZL9' THEN 'New Zealand Subantarctic Islands '
WHEN LEFT(rx_sign, 2) IN ('ZL', 'ZM') THEN 'Nueva Zelanda'
WHEN LEFT(rx_sign, 2) = 'ZP' THEN 'Paraguay'
WHEN LEFT(rx_sign, 2) IN ('ZR','ZS','ZT','ZU') THEN 'Sudafrica'
ELSE 'ZZ - Otros'
END AS pais,
COUNT(DISTINCT rx_sign) AS cantidad
FROM wspr.rx
WHERE tx_sign = 'EA5JTT'
AND time >= '2025-05-23'
AND time < NOW()
GROUP BY pais
ORDER BY pais ASC;

Ejemplo real de salida con un total de 61 territorios DXCC ARRL

row pais cantidad
1 Alemania 281
2 Australia 17
3 Austria 28
4 Belgica 20
5 Bielorusia 2
6 Bosnia-Herzegovina 3
7 Brasil 6
8 Bulgaria 1
9 Caiman 1
10 Canada 32
11 Chequia 12
12 China 2
13 Chipre 1
14 Colombia 1
15 Costa Rica 1
16 Croacia 5
17 Dinamarca 14
18 Escocia 34
19 Eslovaquia 1
20 Eslovenia 6
21 España - Baleares 1
22 España - Canarias 8
23 España - Península 22
24 Estados Unidos 373
25 Estonia 2
26 Filipinas 1
27 Finlandia 20
28 Francia 77
29 Gales 20
30 Gibraltar 1
31 Grecia 27
32 Guersey 1
33 Hungria 10
34 Inglaterra 286
35 Irlanda 5
36 Irlanda del Norte 6
37 Isla Jersey 1
38 Islandia 7
39 Israel 3
40 Italia 59
41 Japon 1
42 Kaliningrado 2
43 Kazaskan 1
44 Letonia 2
45 Luxemburgo 3
46 Malta 1
47 Namibia 1
48 Noruega 17
49 Nueva Zelanda 4
50 Paises Bajos 129
51 Polonia 19
52 Portugal 9
53 Rumania 3
54 Rusia Asiática 1
55 Rusia Europea 1
56 Serbia 2
57 St. Helena 1
58 Suecia 34
59 Suiza 35
60 Turquia 2
61 Ucrania 2
62 ZZ - Otros 23

Algunos paises mas codificados por si quiere hacer una segunda pasada con ellos

SELECT
CASE
WHEN LEFT(rx_sign, 3) IN ('3B8') THEN 'Mauricio'
WHEN LEFT(rx_sign, 3) IN ('3C0') THEN 'Anobon'
WHEN LEFT(rx_sign, 2) IN ('3C') THEN 'Guinea Ecuatorial'
WHEN LEFT(rx_sign, 2) IN ('3V') THEN 'Tunez'
WHEN LEFT(rx_sign, 2) IN ('3W','XV') THEN 'Vietnam'
WHEN LEFT(rx_sign, 2) = '3X' THEN 'Guinea'
WHEN LEFT(rx_sign, 2) IN ('3O','47') THEN 'Montenegro'
WHEN LEFT(rx_sign, 2) IN ('4J','4K') THEN 'Azerbaijan'
WHEN rx_sign REGEXP '^4U[0-9]ITU$' THEN 'ONU (ITU)'
WHEN rx_sign REGEXP '^4U[0-9]UN$' THEN 'ONU (HQ)'
WHEN LEFT(rx_sign, 2) = '4L' THEN 'Georgia'
WHEN LEFT(rx_sign, 2) = '4S' THEN 'SriLanka'
WHEN LEFT(rx_sign, 2) = '5A' THEN 'Libia'
WHEN LEFT(rx_sign, 2) = '5N' THEN 'Nigeria'
WHEN LEFT(rx_sign, 2) = '5R' THEN 'Mauritania'
WHEN LEFT(rx_sign, 2) = '5T' THEN 'Madagascar'
WHEN LEFT(rx_sign, 2) = '5U' THEN 'Niger'
WHEN LEFT(rx_sign, 2) = '5V' THEN 'Togo'
WHEN LEFT(rx_sign, 2) = '5W' THEN 'Samoa'
WHEN LEFT(rx_sign, 2) = '5X' THEN 'Uganda'
WHEN LEFT(rx_sign, 2) IN ('5Y','5Z') THEN 'Kenia'
WHEN LEFT(rx_sign, 2) IN ('6V','6W') THEN 'Senegal'
WHEN LEFT(rx_sign, 2) = '6Y' THEN 'Jamaica'
WHEN LEFT(rx_sign, 2) = '9N' THEN 'Nepal'
WHEN LEFT(rx_sign, 2) IN ('9Q','9R','9S','9T') THEN 'R. D.Congo'
WHEN LEFT(rx_sign, 2) = '9U' THEN 'Burundi'
WHEN LEFT(rx_sign, 2) = 'E3' THEN 'Eritrea'
WHEN LEFT(rx_sign, 2) = 'E4' THEN 'Palestina'
WHEN LEFT(rx_sign, 2) = 'ET' THEN 'Etiopia'
WHEN LEFT(rx_sign, 2) = 'EX' THEN 'Kyrgyzstan'
WHEN LEFT(rx_sign, 2) = 'EY' THEN 'Tajikistan'
WHEN LEFT(rx_sign, 2) = 'EZ' THEN 'Turkmenistan'
WHEN LEFT(rx_sign, 2) = 'E6' THEN 'Niue'
WHEN LEFT(rx_sign, 2) IN ('EP', 'EQ') THEN 'Iran'
WHEN LEFT(rx_sign, 2) = 'HV' THEN 'Vaticano'
WHEN LEFT(rx_sign, 2) IN ('UJ','UK','UL','UM') THEN 'Uzbekistan'
WHEN LEFT(rx_sign, 2) = 'S2' THEN 'Bangladesh'
WHEN LEFT(rx_sign, 2) = 'EK' THEN 'Armenia'
WHEN LEFT(rx_sign, 2) = 'EL' THEN 'Liberia'
WHEN LEFT(rx_sign, 2) = 'ER' THEN 'Moldavia'
WHEN LEFT(rx_sign, 2) = 'S7' THEN 'Seychelles'
WHEN LEFT(rx_sign, 2) = 'S9' THEN 'Sao Tome & Principe'
WHEN LEFT(rx_sign, 2) = 'V2' THEN 'Antigua'
WHEN LEFT(rx_sign, 2) = 'V3' THEN 'Belice'
WHEN LEFT(rx_sign, 2) = 'V4' THEN 'St. Kitts & Nevis'
WHEN LEFT(rx_sign, 2) IN ('RU','RL') THEN 'Rusia Europea'
ELSE 'ZZ - Otros'
END AS pais,
COUNT(DISTINCT rx_sign) AS cantidad
FROM wspr.rx
WHERE tx_sign = 'EA5JTT'
AND time >= '2025-05-23'
AND time < NOW()
GROUP BY pais
ORDER BY pais ASC;

Con una pequeña modificación tendremos la lista de territorios DXCC ARRL como SWL (51 a 9-8-2025):

SELECT
CASE
WHEN LEFT(tx_sign, 2) = '3A' THEN 'Monaco'
WHEN LEFT(tx_sign, 2) = '4M' THEN 'Venezuela'
WHEN LEFT(tx_sign, 2) = '4V' THEN 'Haiti'
WHEN LEFT(tx_sign, 2) IN ('4X','4Z') THEN 'Israel'
WHEN LEFT(tx_sign, 2) IN ('5B', 'C4', 'P3') THEN 'Chipre'
WHEN LEFT(tx_sign, 2) = '5P' THEN 'Dinamarca'
WHEN LEFT(tx_sign, 2) = '9A' THEN 'Croacia'
WHEN LEFT(tx_sign, 2) = '9H' THEN 'Malta'
WHEN LEFT(tx_sign, 2) = 'C3' THEN 'Andorra'
WHEN LEFT(tx_sign, 2) IN ('BU','BV','BW','BX') THEN 'Taiwan'
WHEN LEFT(tx_sign, 1) = 'B' THEN 'China'
WHEN LEFT(tx_sign, 2) IN ('CA', 'CB', 'CC', 'CD', 'CE') THEN 'Chile'
WHEN LEFT(tx_sign, 2) = 'CN' THEN 'Marruecos'
WHEN LEFT(tx_sign, 2) IN ('CS','CT') THEN 'Portugal'
WHEN LEFT(tx_sign, 2) IN ('DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ','DR') THEN 'Alemania'
WHEN LEFT(tx_sign, 2) IN ('DU','DV','DW','DX','DY','DZ','4D','4E','4F','4G','4H','4I') THEN 'Filipinas'
WHEN LEFT(tx_sign, 3) IN ('EA6', 'EB6', 'EC6', 'ED6', 'EE6', 'EF6', 'EG6','EH6') THEN 'España - Baleares'
WHEN LEFT(tx_sign, 3) IN ('EA8', 'EB8', 'EC8', 'ED8', 'EE8', 'EF8', 'EG8','EH8') THEN 'España - Canarias'
WHEN LEFT(tx_sign, 3) IN ('EA9', 'EB9', 'EC9', 'ED9', 'EE9', 'EF9', 'EG9','EH9') THEN 'España - Ceuta/Melilla'
WHEN LEFT(tx_sign, 2) IN ('EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG','EH') THEN 'España - Península'
WHEN LEFT(tx_sign, 2) = 'E5' THEN 'Islas Cook'
WHEN LEFT(tx_sign, 2) = 'E7' THEN 'Bosnia-Herzegovina'
WHEN LEFT(tx_sign, 2) = 'EW' THEN 'Bielorusia'
WHEN LEFT(tx_sign, 2) IN ('EI', 'EJ') THEN 'Irlanda'
WHEN LEFT(tx_sign, 2) = 'ES' THEN 'Estonia'
WHEN LEFT(tx_sign, 1) = 'F' THEN 'Francia'
WHEN LEFT(tx_sign, 2) IN ('GB', 'GX', 'MX','2E') THEN 'Inglaterra'
WHEN LEFT(tx_sign, 2) IN ('GI', 'GN', 'MI','2I') THEN 'Irlanda del Norte'
WHEN LEFT(tx_sign, 2) IN ('GJ', 'GH', 'MJ','2J') THEN 'Isla Jersey'
WHEN LEFT(tx_sign, 2) IN ('GM', 'GS', 'MM','2M') THEN 'Escocia'
WHEN LEFT(tx_sign, 2) IN ('GU', 'GP', 'MU','2U') THEN 'Guersey'
WHEN LEFT(tx_sign, 2) IN ('GW', 'GC', 'MW','2W') THEN 'Gales'
WHEN LEFT(tx_sign, 2) IN ('GD', 'GT', 'MD','2D') THEN 'Isla de Man'
WHEN LEFT(tx_sign, 1) = 'G' AND SUBSTRING(tx_sign, 2, 1) REGEXP '[0-9]' THEN 'Inglaterra'
WHEN LEFT(tx_sign, 2) IN ('HA', 'HG') THEN 'Hungria'
WHEN LEFT(tx_sign, 2) IN ('HC', 'HD') THEN 'Ecuador'
WHEN LEFT(tx_sign, 2) IN ('HJ', 'HK') THEN 'Colombia'
WHEN LEFT(tx_sign, 2) IN ('HO', 'HP') THEN 'Panama'
WHEN LEFT(tx_sign, 2) IN ('HQ', 'HR') THEN 'Honduras'
WHEN LEFT(tx_sign, 2) = 'HB' THEN 'Suiza'
WHEN LEFT(tx_sign, 2) = 'HH' THEN 'Haiti'
WHEN LEFT(tx_sign, 2) = 'HI' THEN 'Republica Dominicana'
WHEN LEFT(tx_sign, 2) = 'HL' THEN 'Republica Corea'
WHEN LEFT(tx_sign, 2) = 'HV' THEN 'Vaticano'
WHEN LEFT(tx_sign, 3) IN ('IS0', 'IM0') THEN 'Cerdeña'
WHEN LEFT(tx_sign, 1) = 'I' THEN 'Italia'
WHEN LEFT(tx_sign, 2) IN ('LA', 'LB','LC', 'LD', 'LE', 'LF', 'LG', 'LH', 'LI', 'LJ', 'LK', 'LL', 'LM', 'LN') THEN 'Noruega'
WHEN LEFT(tx_sign, 2) IN ('JA','JB','JC','JD','JE','JF','JG','JH','JI','JJ','JK','JL','JM','JN','JO','JP','JQ','JR','JS','7J','7K','7L','7N') THEN 'Japon'
WHEN LEFT(tx_sign, 2) = 'LX' THEN 'Luxemburgo'
WHEN LEFT(tx_sign, 2) = 'LZ' THEN 'Bulgaria'
WHEN LEFT(tx_sign, 1) = 'M' AND SUBSTRING(tx_sign, 2, 1) REGEXP '[0-9]' THEN 'Inglaterra'
WHEN LEFT(tx_sign, 2) = 'V5' THEN 'Namibia'
WHEN LEFT(tx_sign, 2) = 'OE' THEN 'Austria'
WHEN LEFT(tx_sign, 2) IN ('OK', 'OL') THEN 'Chequia'
WHEN LEFT(tx_sign, 2) IN ('OF', 'OG', 'OH', 'OI') THEN 'Finlandia'
WHEN LEFT(tx_sign, 2) = 'OM' THEN 'Eslovaquia'
WHEN LEFT(tx_sign, 2) IN ('ON', 'OO', 'OP','OQ', 'OR', 'OS','OT') THEN 'Belgica'
WHEN LEFT(tx_sign, 2) IN ('OU', 'OV', 'OW','OZ') THEN 'Dinamarca'
WHEN LEFT(tx_sign, 2) IN ('PA', 'PB', 'PC','PD', 'PE', 'PF', 'PG', 'PH', 'PI') THEN 'Paises Bajos'
WHEN LEFT(tx_sign, 2) IN ('PP','PQ','PR','PS','PT','PU','PV','PW','PX','PY','ZV','ZW','ZX','ZY','ZZ') THEN 'Brasil'
WHEN LEFT(tx_sign, 2) = 'PZ' THEN 'Surinam'
WHEN LEFT(tx_sign, 2) IN ('R1','R3','R4','R5','R6','R7') THEN 'Rusia Europea'
WHEN LEFT(tx_sign, 2) = 'R2' THEN 'Kaliningrado'
WHEN LEFT(tx_sign, 2) IN ('R8','R9','R0') THEN 'Rusia Asiática'
WHEN LEFT(tx_sign, 2) IN ('UA1','UA3','UA4','UA5','UA6','UA7','RA1','RA3','RA4','RA5','RA6','RA7') THEN 'Rusia Europea'
WHEN LEFT(tx_sign, 3) IN ('UA2', 'RA2') THEN 'Kaliningrado'
WHEN LEFT(tx_sign, 2) IN ('UA8','UA9','UA0','RA8','RA9','RA0') THEN 'Rusia Asiática'
WHEN LEFT(tx_sign, 2) IN ('UN','UM','UO','UP','UQ') THEN 'Kazaskan'
WHEN LEFT(tx_sign, 2) = 'S5' THEN 'Eslovenia'
WHEN LEFT(tx_sign, 2) IN ('SA','SB','SC', 'SD', 'SE', 'SF', 'SG', 'SH', 'SI', 'SJ', 'SK', 'SL', 'SM', '7S','8S') THEN 'Suecia'
WHEN LEFT(tx_sign, 2) IN ('SN', 'SO', 'SP','SQ', 'SR') THEN 'Polonia'
WHEN LEFT(tx_sign, 2) IN ('SV', 'SW', 'SX','SY', 'SZ', 'J4') THEN 'Grecia'
WHEN LEFT(tx_sign, 2) IN ('TA', 'TB', 'TC') THEN 'Turquia'
WHEN LEFT(tx_sign, 2) IN ('TI', 'TE') THEN 'Costa Rica'
WHEN LEFT(tx_sign, 2) = 'TF' THEN 'Islandia'
WHEN LEFT(tx_sign, 2) = 'TK' THEN 'Corcega'
WHEN LEFT(tx_sign, 2) IN ('UR','US', 'UT', 'UU', 'UV', 'UW', 'UX', 'UY', 'UZ') THEN 'Ucrania'
WHEN LEFT(tx_sign, 2) IN ('VA', 'VB', 'VC', 'VD', 'VE', 'VF', 'VG','VO','VP','VQ','VR','VS','VT','VU','VV','VX','VY') THEN 'Canada'
WHEN LEFT(tx_sign, 2) IN ('VK', 'AX') THEN 'Australia'
WHEN LEFT(tx_sign, 1) IN ('K', 'N', 'W') THEN 'Estados Unidos'
WHEN LEFT(tx_sign, 2) IN ('AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI' , 'AJ', 'AK') THEN 'Estados Unidos'
WHEN LEFT(tx_sign, 2) = 'YL' THEN 'Letonia'
WHEN LEFT(tx_sign, 2) = 'YM' THEN 'Turquia'
WHEN LEFT(tx_sign, 2) IN ('YO', 'YP', 'YQ', 'YR') THEN 'Rumania'
WHEN LEFT(tx_sign, 2) IN ('YT', 'YU') THEN 'Serbia'
WHEN LEFT(tx_sign, 2) = 'ZA' THEN 'Albania'
WHEN LEFT(tx_sign, 3) = 'ZB2' THEN 'Gibraltar'
WHEN LEFT(tx_sign, 3) = 'ZD7' THEN 'St. Helena'
WHEN LEFT(tx_sign, 2) = 'ZF' THEN 'Caiman'
WHEN LEFT(tx_sign, 3) = 'ZL7' THEN ' Chatham '
WHEN LEFT(tx_sign, 3) = 'ZL8' THEN 'Kermadec'
WHEN LEFT(tx_sign, 3) = 'ZL9' THEN 'New Zealand Subantarctic Islands '
WHEN LEFT(tx_sign, 2) IN ('ZL', 'ZM') THEN 'Nueva Zelanda'
WHEN LEFT(tx_sign, 2) = 'ZP' THEN 'Paraguay'
WHEN LEFT(tx_sign, 2) IN ('ZR','ZS','ZT','ZU') THEN 'Sudafrica'
ELSE 'ZZ - Otros'
END AS pais,
COUNT(DISTINCT tx_sign) AS cantidad
FROM wspr.rx
WHERE rx_sign = 'EA5JTT'
AND time >= '2025-05-23'
AND time < NOW()
GROUP BY pais
ORDER BY pais ASC

Para afinar la SELECT se puede usar un código que permita listar el grupo ZZ-Otros donde encontraremos territorios que no han sido tratados y tambien indicativos erroneos

SELECT DISTINCT tx_sign
FROM (
SELECT
tx_sign,
CASE
WHEN LEFT(tx_sign, 2) = '3A' THEN 'Monaco'
WHEN LEFT(tx_sign, 2) IN ('4J','4K') THEN 'Azerbaijan'
WHEN LEFT(tx_sign, 2) = '4L' THEN 'Georgia'
WHEN LEFT(tx_sign, 2) IN ('4X','4Z') THEN 'Israel'
WHEN LEFT(tx_sign, 2) IN ('5B', 'C4', 'P3') THEN 'Chipre'
WHEN LEFT(tx_sign, 2) = '5P' THEN 'Dinamarca'
WHEN LEFT(tx_sign, 2) = '9A' THEN 'Croacia'
WHEN LEFT(tx_sign, 2) = '9H' THEN 'Malta'
WHEN LEFT(tx_sign, 2) = 'C3' THEN 'Andorra'
WHEN LEFT(tx_sign, 2) IN ('CA', 'CB', 'CC', 'CD', 'CE') THEN 'Chile'
WHEN LEFT(tx_sign, 2) = 'CN' THEN 'Marruecos'
WHEN LEFT(tx_sign, 2) IN ('CS','CT') THEN 'Portugal'
WHEN LEFT(tx_sign, 2) IN ('DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ','DR') THEN 'Alemania'
WHEN LEFT(tx_sign, 3) IN ('EA6', 'EB6', 'EC6', 'ED6', 'EE6', 'EF6', 'EG6','EH6') THEN 'España - Baleares'
WHEN LEFT(tx_sign, 3) IN ('EA8', 'EB8', 'EC8', 'ED8', 'EE8', 'EF8', 'EG8','EH8') THEN 'España - Canarias'
WHEN LEFT(tx_sign, 3) IN ('EA9', 'EB9', 'EC9', 'ED9', 'EE9', 'EF9', 'EG9','EH9') THEN 'España - Ceuta/Melilla'
WHEN LEFT(tx_sign, 2) IN ('EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG','EH') THEN 'España - Península'
WHEN LEFT(tx_sign, 2) IN ('EI', 'EJ') THEN 'Irlanda'
WHEN LEFT(tx_sign, 2) = 'EK' THEN 'Armenia'
WHEN LEFT(tx_sign, 2) = 'ES' THEN 'Estonia'
WHEN LEFT(tx_sign, 1) = 'F' THEN 'Francia'
WHEN LEFT(tx_sign, 3) = '2M0' THEN 'Escocia'
WHEN LEFT(tx_sign, 3) IN ('2E0', '2E1') THEN 'Inglaterra'
WHEN LEFT(tx_sign, 2) IN ('GI', 'GN') THEN 'Irlanda del Norte'
WHEN LEFT(tx_sign, 2) IN ('GJ', 'GH') THEN 'Isla Jersey'
WHEN LEFT(tx_sign, 2) IN ('GM', 'GS', 'MM') THEN 'Escocia'
WHEN LEFT(tx_sign, 2) IN ('GU', 'GP') THEN 'Guersey'
WHEN LEFT(tx_sign, 2) IN ('GW', 'GC', 'MW') THEN 'Gales'
WHEN LEFT(tx_sign, 2) IN ('GD', 'GT') THEN 'Isla de Man'
WHEN LEFT(tx_sign, 2) IN ('GB', 'GX') THEN 'Inglaterra'
WHEN LEFT(tx_sign, 1) = 'G' AND SUBSTRING(tx_sign, 2, 1) REGEXP '[0-9]' THEN 'Inglaterra'
WHEN LEFT(tx_sign, 2) IN ('HA', 'HG') THEN 'Hungria'
WHEN LEFT(tx_sign, 2) IN ('HC', 'HD') THEN 'Ecuador'
WHEN LEFT(tx_sign, 2) IN ('HJ', 'HK') THEN 'Colombia'
WHEN LEFT(tx_sign, 2) IN ('HO', 'HP') THEN 'Panama'
WHEN LEFT(tx_sign, 2) IN ('HQ', 'HR') THEN 'Honduras'
WHEN LEFT(tx_sign, 2) = 'HB' THEN 'Suiza'
WHEN LEFT(tx_sign, 2) = 'HH' THEN 'Haiti'
WHEN LEFT(tx_sign, 2) = 'HI' THEN 'Republica Dominicana'
WHEN LEFT(tx_sign, 2) = 'HL' THEN 'Republica Corea'
WHEN LEFT(tx_sign, 2) = 'HV' THEN 'Vaticano'
WHEN LEFT(tx_sign, 3) IN ('IS0', 'IM0') THEN 'Cerdeña'
WHEN LEFT(tx_sign, 1) = 'I' THEN 'Italia'
WHEN LEFT(tx_sign, 2) IN ('LA', 'LB','LC', 'LD', 'LE', 'LF', 'LG', 'LH', 'LI', 'LJ', 'LK', 'LL', 'LM', 'LN') THEN 'Noruega'
WHEN LEFT(tx_sign, 2) = 'LX' THEN 'Luxemburgo'
WHEN LEFT(tx_sign, 2) = 'LZ' THEN 'Bulgaria'
WHEN LEFT(tx_sign, 1) = 'M' AND SUBSTRING(tx_sign, 2, 1) REGEXP '[0-9]' THEN 'Inglaterra'
WHEN LEFT(tx_sign, 2) = 'OE' THEN 'Austria'
WHEN LEFT(tx_sign, 2) IN ('OK', 'OL') THEN 'Chequia'
WHEN LEFT(tx_sign, 2) IN ('OF', 'OG', 'OH', 'OI') THEN 'Finlandia'
WHEN LEFT(tx_sign, 2) = 'OM' THEN 'Eslovaquia'
WHEN LEFT(tx_sign, 2) IN ('ON', 'OO', 'OP','OQ', 'OR', 'OS','OT') THEN 'Belgica'
WHEN LEFT(tx_sign, 2) IN ('OU', 'OV', 'OW','OZ') THEN 'Dinamarca'
WHEN LEFT(tx_sign, 2) IN ('PA', 'PB', 'PC','PD', 'PE', 'PF', 'PG', 'PH', 'PI') THEN 'Paises Bajos'
WHEN LEFT(tx_sign, 2) IN ('PP','PQ','PR','PS','PT','PU','PV','PW','PX','PY','ZV','ZW','ZX','ZY','ZZ') THEN 'Brasil'
WHEN LEFT(tx_sign, 2) = 'R2' THEN 'Kaliningrado'
WHEN LEFT(tx_sign, 2) = 'R4' THEN 'Rusia Europea'
WHEN LEFT(tx_sign, 2) = 'R8' THEN 'Rusia Asiática'
WHEN LEFT(tx_sign, 3) IN ('UA2', 'RA2') THEN 'Kaliningrado'
WHEN LEFT(tx_sign, 2) = 'S5' THEN 'Eslovenia'
WHEN LEFT(tx_sign, 2) IN ('SA','SB','SC', 'SD', 'SE', 'SF', 'SG', 'SH', 'SI', 'SJ', 'SK', 'SL', 'SM', '7S','8S') THEN 'Suecia'
WHEN LEFT(tx_sign, 2) IN ('SN', 'SO', 'SP','SQ', 'SR') THEN 'Polonia'
WHEN LEFT(tx_sign, 2) IN ('SV', 'SW', 'SX','SY', 'SZ', 'J4') THEN 'Grecia'
WHEN LEFT(tx_sign, 2) IN ('TA', 'TB', 'TC') THEN 'Turquia'
WHEN LEFT(tx_sign, 2) = 'TF' THEN 'Islandia'
WHEN LEFT(tx_sign, 2) IN ('UR','US', 'UT', 'UU', 'UV', 'UW', 'UX', 'UY', 'UZ') THEN 'Ucrania'
WHEN LEFT(tx_sign, 2) IN ('VA', 'VB', 'VC', 'VD', 'VE', 'VF', 'VG') THEN 'Canada'
WHEN LEFT(tx_sign, 2) IN ('VK', 'AX') THEN 'Australia'
WHEN LEFT(tx_sign, 1) IN ('K', 'N', 'W') THEN 'Estados Unidos'
WHEN LEFT(tx_sign, 2) IN ('AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI' , 'AJ', 'AK') THEN 'Estados Unidos'
WHEN LEFT(tx_sign, 2) = 'YL' THEN 'Letonia'
WHEN LEFT(tx_sign, 2) IN ('YO', 'YP', 'YQ', 'YR') THEN 'Rumania'
WHEN LEFT(tx_sign, 2) IN ('YT', 'YU') THEN 'Serbia'
WHEN LEFT(tx_sign, 2) = 'ZA' THEN 'Albania'
WHEN LEFT(tx_sign, 3) = 'ZB2' THEN 'Gibraltar'
WHEN LEFT(tx_sign, 3) = 'ZD7' THEN 'St. Helena'
WHEN LEFT(tx_sign, 2) IN ('ZL', 'ZM') THEN 'Nueva Zelanda'
ELSE 'ZZ - Otros'
END AS pais
FROM wspr.rx
WHERE rx_sign = 'EA5JTT'
AND time >= '2025-06-01'
AND time < NOW()
) AS sub
WHERE pais = 'ZZ - Otros'
ORDER BY tx_sign;

Espero que les resulte de utilidad, y que me superen en DXCC WSPR tanto en TX como en SWL

EA5JTT

WSPR & DXCC

No hay comentarios:

Publicar un comentario

ATS-mini SI4732: lenguaje de script

Denunciar abuso