Skip to content

Instantly share code, notes, and snippets.

@mrog mrog/ham_stat.sql

Last active Sep 6, 2019
Embed
What would you like to do?
-- 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
You can’t perform that action at this time.