Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement