SQL queries to gather ham callsign statistics for https://ham.stackexchange.com/questions/15262/what-fraction-of-2x2-usa-call-signs-are-vanity-calls

ham_stat.sql

-- 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;