SQL queries to gather ham callsign statistics for https://ham.stackexchange.com/questions/15262/what-fraction-of-2x2-usa-call-signs-are-vanity-calls
-- Total number of active callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' | |
-- Number of active 2X2 vanity callsigns for individuals | |
-- See query in next section | |
-- Number of active 2X2 non-vanity callsigns for individuals | |
-- See query in next section | |
-- Number of active 1X2 vanity callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{1}[0-9][A-Z]{2}[ ]*$' and hd.radio_service_code = 'HV'; | |
-- Number of active 1X2 non-vanity callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{1}[0-9][A-Z]{2}[ ]*$' and hd.radio_service_code = 'HA'; | |
-- Number of active 2X1 vanity callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{1}[ ]*$' and hd.radio_service_code = 'HV'; | |
-- Number of active 2X1 non-vanity callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{1}[ ]*$' and hd.radio_service_code = 'HA'; | |
-- Number of active 2X3 vanity callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{3}[ ]*$' and hd.radio_service_code = 'HV'; | |
-- Number of active 2X3 non-vanity callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{3}[ ]*$' and hd.radio_service_code = 'HA'; | |
-- Number of active 1X3 vanity callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{1}[0-9][A-Z]{3}[ ]*$' and hd.radio_service_code = 'HV'; | |
-- Number of active 1X3 non-vanity callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{1}[0-9][A-Z]{3}[ ]*$' and hd.radio_service_code = 'HA'; | |
------------------------------------------------------------------------------------------------------------------------------------------------- | |
-- Total number of active 2X2 callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{2}[ ]*$'; | |
-- Number of active 2X2 vanity callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{2}[ ]*$' and hd.radio_service_code = 'HV'; | |
-- Number of active 2X2 non-vanity callsigns for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{2}[ ]*$' and hd.radio_service_code = 'HA'; | |
-- Number of active 2X2 callsigns for individuals with no previous callsign or operator class | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{2}[ ]*$' and am.previous_callsign is null and am.previous_operator_class is null; | |
-- Number of active 2X2 vanity callsigns for individuals with no previous callsign or operator class | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{2}[ ]*$' and hd.radio_service_code = 'HV' and am.previous_callsign is null and am.previous_operator_class is null; | |
-- Number of active 2X2 non-vanity callsigns for individuals with no previous callsign or operator class | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{2}[ ]*$' and hd.radio_service_code = 'HA' and am.previous_callsign is null and am.previous_operator_class is null; | |
------------------------------------------------------------------------------------------------------------------------------------------------- | |
-- Total Number of active extra or advanced licenses for individuals | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and am.operator_class in ('E', 'A') and hd.license_status = 'A'; | |
-- Number of active extra or advanced licenses for individuals with vanity callsigns | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and am.operator_class in ('E', 'A') and hd.license_status = 'A' and hd.radio_service_code = 'HV'; | |
-- Number of active extra or advanced licenses for individuals with non-vanity callsigns | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and am.operator_class in ('E', 'A') and hd.license_status = 'A' and hd.radio_service_code = 'HA'; | |
-- Number of active extra or advanced licenses for individuals with no previous callsign or operator class | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and am.operator_class in ('E', 'A') and hd.license_status = 'A' and am.previous_callsign is null and am.previous_operator_class is null; | |
-- Number of active extra or advanced licenses for individuals with vanity callsigns and no previous callsign or operator class | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and am.operator_class in ('E', 'A') and hd.license_status = 'A' and hd.radio_service_code = 'HV' and am.previous_callsign is null and am.previous_operator_class is null; | |
-- Number of active extra or advanced licenses for individuals with non-vanity callsigns and no previous callsign or operator class | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and am.operator_class in ('E', 'A') and hd.license_status = 'A' and hd.radio_service_code = 'HA' and am.previous_callsign is null and am.previous_operator_class is null; | |
-- Number of active extra or advanced licenses for individuals with 2X2 callsigns | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and am.operator_class in ('E', 'A') and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{2}[ ]*$'; | |
-- Number of active extra or advanced licenses for individuals with vanity 2X2 callsigns | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and am.operator_class in ('E', 'A') and hd.license_status = 'A' and hd.radio_service_code = 'HV' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{2}[ ]*$'; | |
-- Number of active extra or advanced licenses for individuals with non-vanity 2X2 callsigns | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and am.operator_class in ('E', 'A') and hd.license_status = 'A' and hd.radio_service_code = 'HA' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{2}[ ]*$'; | |
-- Number of active extra or advanced licenses for individuals with 2X2 callsigns and no previous callsign or operator class | |
select count(*) | |
from dbo.pubacc_am am | |
join dbo.pubacc_hd hd on am.unique_system_identifier = hd.unique_system_identifier | |
join dbo.pubacc_en en on en.unique_system_identifier = hd.unique_system_identifier | |
where en.applicant_type_code = 'I' and am.operator_class in ('E', 'A') and hd.license_status = 'A' and am.callsign ~ '^[A-Z]{2}[0-9][A-Z]{2}[ ]*$' and am.previous_callsign is null and am.previous_operator_class is null; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment