#   qryStatIQVFrequencyByCounty.sql
#   SQL
#   n6lhv@arrl.net (Wayne Smith)

#   IQV = (total observed variation) / (maximum possible variation)
#   IQV = [ k * ( k - 1 ) ] * [ 1 - ( sum for 1 to k ( squared proportions of the frequency counts )) ]
#     where k = number of categories in the variable, and
#           n = total number of cases for the variable


#   Display the MySQL version
SELECT version();


#  Switch to the correct database
USE uls


#  delete the table if it exists (it probably does)
DROP TABLE IF EXISTS tblStatIQVFrequencyBandByCounty;
CREATE TABLE tblStatIQVFrequencyBandByCounty
(
      County                    char(20)             not null,
      IQV                       numeric(4,2)         not null
);

#  the table is used as a report, so let's identify the field
INSERT INTO tblStatIQVFrequencyBandByCounty
  SET County = "SOCAL COUNTY"
;


# Do a single County
SET @County := 'IMPERIAL';

#  delete the table if it exists (it probably does)
DROP TABLE IF EXISTS tblStatIQVTempFrequencyBand;
DROP TABLE IF EXISTS tblStatIQVTempCount;
DROP TABLE IF EXISTS tblStatIQVTempProportion;
DROP TABLE IF EXISTS tblStatIQVTempProportionCheck;
DROP TABLE IF EXISTS tblStatIQVTempKCheck;


#  subselect the needed field for the specified County
CREATE TABLE tblStatIQVTempFrequencyBand
  SELECT DISTINCT frequency_assigned

  FROM tblGeoStateCA

  WHERE location_county = @County

  GROUP BY frequency_assigned
;

#  we'll need a new column to hold the Frequency Band
ALTER TABLE tblStatIQVTempFrequencyBand
  ADD COLUMN
(
      FrequencyBand             char(20)             not null
);

#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '025 - 050 MHz'
  WHERE (frequency_assigned >= '00000025.00000000' AND
         frequency_assigned <= '00000050.00000000')
;
#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '108 - 136 MHz'
  WHERE (frequency_assigned >= '00000108.00000000' AND
         frequency_assigned <= '00000136.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '150 - 174 MHz'
  WHERE (frequency_assigned >= '00000150.00000000' AND
         frequency_assigned <= '00000174.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '216 - 220 MHz'
  WHERE (frequency_assigned >= '00000216.00000000' AND
         frequency_assigned <= '00000220.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '450 - 512 MHz'
  WHERE (frequency_assigned >= '00000450.00000000' AND
         frequency_assigned <= '00000512.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '806 - 870 MHz'
  WHERE (frequency_assigned >= '00000806.00000000' AND
         frequency_assigned <= '00000870.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '896 - 940 MHz'
  WHERE (frequency_assigned >= '00000896.00000000' AND
         frequency_assigned <= '00000940.00000000')
;

#  delete frequencies that didn't fall into any of the above bands
DELETE
  FROM tblStatIQVTempFrequencyBand

  WHERE FrequencyBand = ''
;

#  count unique (radio) frequencies by county
CREATE TABLE tblStatIQVTempCount
  SELECT FrequencyBand, count( DISTINCT frequency_assigned ) AS CountOfUniqueFrequency_Assigned

  FROM tblStatIQVTempFrequencyBand

  GROUP BY FrequencyBand
;

#  derive squared proportions for the frequency distributions (part 1 of 2)
SELECT @SumOfCountOfUniqueFrequency_Assigned := sum( CountOfUniqueFrequency_Assigned )

FROM tblStatIQVTempCount
;

#  check it
CREATE TABLE tblStatIQVTempProportionCheck
  SELECT @SumOfCountOfUniqueFrequency_Assigned
;

#  derive squared proportions for the frequency distributions (part 2 of 2)
CREATE TABLE tblStatIQVTempProportion
  SELECT *, pow(( CountOfUniqueFrequency_Assigned / @SumOfCountOfUniqueFrequency_Assigned ), 2 )
         AS SquaredProportion

  FROM tblStatIQVTempCount
;

#  derive Index of Diversity (D)
SELECT @D := 1 - sum( SquaredProportion )

FROM tblStatIQVTempProportion
;

#  derive K
SELECT @CountOfUniqueFrequency:=count( FrequencyBand )

FROM tblStatIQVTempFrequency
;

#  check it
CREATE TABLE tblStatIQVTempKCheck
  SELECT @CountOfUniqueFrequency
;

#  derive Index of Qualitative Variation (IQV)
SET @IQV := ( @CountOfUniqueFrequency / ( @CountOfUniqueFrequency - 1 ))
               * @D * 100
;

#  record the value
INSERT INTO tblStatIQVFrequencyBandByCounty
  SET County = @County,
      IQV = @IQV
;


# Do a single County
SET @County := 'KERN';

#  delete the table if it exists (it probably does)
DROP TABLE IF EXISTS tblStatIQVTempFrequencyBand;
DROP TABLE IF EXISTS tblStatIQVTempCount;
DROP TABLE IF EXISTS tblStatIQVTempProportion;
DROP TABLE IF EXISTS tblStatIQVTempProportionCheck;
DROP TABLE IF EXISTS tblStatIQVTempKCheck;


#  subselect the needed field for the specified County
CREATE TABLE tblStatIQVTempFrequencyBand
  SELECT DISTINCT frequency_assigned

  FROM tblGeoStateCA

  WHERE location_county = @County

  GROUP BY frequency_assigned
;

#  we'll need a new column to hold the Frequency Band
ALTER TABLE tblStatIQVTempFrequencyBand
  ADD COLUMN
(
      FrequencyBand             char(20)             not null
);

#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '025 - 050 MHz'
  WHERE (frequency_assigned >= '00000025.00000000' AND
         frequency_assigned <= '00000050.00000000')
;
#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '108 - 136 MHz'
  WHERE (frequency_assigned >= '00000108.00000000' AND
         frequency_assigned <= '00000136.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '150 - 174 MHz'
  WHERE (frequency_assigned >= '00000150.00000000' AND
         frequency_assigned <= '00000174.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '216 - 220 MHz'
  WHERE (frequency_assigned >= '00000216.00000000' AND
         frequency_assigned <= '00000220.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '450 - 512 MHz'
  WHERE (frequency_assigned >= '00000450.00000000' AND
         frequency_assigned <= '00000512.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '806 - 870 MHz'
  WHERE (frequency_assigned >= '00000806.00000000' AND
         frequency_assigned <= '00000870.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '896 - 940 MHz'
  WHERE (frequency_assigned >= '00000896.00000000' AND
         frequency_assigned <= '00000940.00000000')
;

#  delete frequencies that didn't fall into any of the above bands
DELETE
  FROM tblStatIQVTempFrequencyBand

  WHERE FrequencyBand = ''
;

#  count unique (radio) frequencies by county
CREATE TABLE tblStatIQVTempCount
  SELECT FrequencyBand, count( DISTINCT frequency_assigned ) AS CountOfUniqueFrequency_Assigned

  FROM tblStatIQVTempFrequencyBand

  GROUP BY FrequencyBand
;

#  derive squared proportions for the frequency distributions (part 1 of 2)
SELECT @SumOfCountOfUniqueFrequency_Assigned := sum( CountOfUniqueFrequency_Assigned )

FROM tblStatIQVTempCount
;

#  check it
CREATE TABLE tblStatIQVTempProportionCheck
  SELECT @SumOfCountOfUniqueFrequency_Assigned
;

#  derive squared proportions for the frequency distributions (part 2 of 2)
CREATE TABLE tblStatIQVTempProportion
  SELECT *, pow(( CountOfUniqueFrequency_Assigned / @SumOfCountOfUniqueFrequency_Assigned ), 2 )
         AS SquaredProportion

  FROM tblStatIQVTempCount
;

#  derive Index of Diversity (D)
SELECT @D := 1 - sum( SquaredProportion )

FROM tblStatIQVTempProportion
;

#  derive K
SELECT @CountOfUniqueFrequency:=count( FrequencyBand )

FROM tblStatIQVTempFrequency
;

#  check it
CREATE TABLE tblStatIQVTempKCheck
  SELECT @CountOfUniqueFrequency
;

#  derive Index of Qualitative Variation (IQV)
SET @IQV := ( @CountOfUniqueFrequency / ( @CountOfUniqueFrequency - 1 ))
               * @D * 100
;

#  record the value
INSERT INTO tblStatIQVFrequencyBandByCounty
  SET County = @County,
      IQV = @IQV
;


# Do a single County
SET @County := 'LOS ANGELES';

#  delete the table if it exists (it probably does)
DROP TABLE IF EXISTS tblStatIQVTempFrequencyBand;
DROP TABLE IF EXISTS tblStatIQVTempCount;
DROP TABLE IF EXISTS tblStatIQVTempProportion;
DROP TABLE IF EXISTS tblStatIQVTempProportionCheck;
DROP TABLE IF EXISTS tblStatIQVTempKCheck;


#  subselect the needed field for the specified County
CREATE TABLE tblStatIQVTempFrequencyBand
  SELECT DISTINCT frequency_assigned

  FROM tblGeoStateCA

  WHERE location_county = @County

  GROUP BY frequency_assigned
;

#  we'll need a new column to hold the Frequency Band
ALTER TABLE tblStatIQVTempFrequencyBand
  ADD COLUMN
(
      FrequencyBand             char(20)             not null
);

#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '025 - 050 MHz'
  WHERE (frequency_assigned >= '00000025.00000000' AND
         frequency_assigned <= '00000050.00000000')
;
#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '108 - 136 MHz'
  WHERE (frequency_assigned >= '00000108.00000000' AND
         frequency_assigned <= '00000136.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '150 - 174 MHz'
  WHERE (frequency_assigned >= '00000150.00000000' AND
         frequency_assigned <= '00000174.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '216 - 220 MHz'
  WHERE (frequency_assigned >= '00000216.00000000' AND
         frequency_assigned <= '00000220.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '450 - 512 MHz'
  WHERE (frequency_assigned >= '00000450.00000000' AND
         frequency_assigned <= '00000512.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '806 - 870 MHz'
  WHERE (frequency_assigned >= '00000806.00000000' AND
         frequency_assigned <= '00000870.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '896 - 940 MHz'
  WHERE (frequency_assigned >= '00000896.00000000' AND
         frequency_assigned <= '00000940.00000000')
;

#  delete frequencies that didn't fall into any of the above bands
DELETE
  FROM tblStatIQVTempFrequencyBand

  WHERE FrequencyBand = ''
;

#  count unique (radio) frequencies by county
CREATE TABLE tblStatIQVTempCount
  SELECT FrequencyBand, count( DISTINCT frequency_assigned ) AS CountOfUniqueFrequency_Assigned

  FROM tblStatIQVTempFrequencyBand

  GROUP BY FrequencyBand
;

#  derive squared proportions for the frequency distributions (part 1 of 2)
SELECT @SumOfCountOfUniqueFrequency_Assigned := sum( CountOfUniqueFrequency_Assigned )

FROM tblStatIQVTempCount
;

#  check it
CREATE TABLE tblStatIQVTempProportionCheck
  SELECT @SumOfCountOfUniqueFrequency_Assigned
;

#  derive squared proportions for the frequency distributions (part 2 of 2)
CREATE TABLE tblStatIQVTempProportion
  SELECT *, pow(( CountOfUniqueFrequency_Assigned / @SumOfCountOfUniqueFrequency_Assigned ), 2 )
         AS SquaredProportion

  FROM tblStatIQVTempCount
;

#  derive Index of Diversity (D)
SELECT @D := 1 - sum( SquaredProportion )

FROM tblStatIQVTempProportion
;

#  derive K
SELECT @CountOfUniqueFrequency:=count( FrequencyBand )

FROM tblStatIQVTempFrequency
;

#  check it
CREATE TABLE tblStatIQVTempKCheck
  SELECT @CountOfUniqueFrequency
;

#  derive Index of Qualitative Variation (IQV)
SET @IQV := ( @CountOfUniqueFrequency / ( @CountOfUniqueFrequency - 1 ))
               * @D * 100
;

#  record the value
INSERT INTO tblStatIQVFrequencyBandByCounty
  SET County = @County,
      IQV = @IQV
;


# Do a single County
SET @County := 'ORANGE';

#  delete the table if it exists (it probably does)
DROP TABLE IF EXISTS tblStatIQVTempFrequencyBand;
DROP TABLE IF EXISTS tblStatIQVTempCount;
DROP TABLE IF EXISTS tblStatIQVTempProportion;
DROP TABLE IF EXISTS tblStatIQVTempProportionCheck;
DROP TABLE IF EXISTS tblStatIQVTempKCheck;


#  subselect the needed field for the specified County
CREATE TABLE tblStatIQVTempFrequencyBand
  SELECT DISTINCT frequency_assigned

  FROM tblGeoStateCA

  WHERE location_county = @County

  GROUP BY frequency_assigned
;

#  we'll need a new column to hold the Frequency Band
ALTER TABLE tblStatIQVTempFrequencyBand
  ADD COLUMN
(
      FrequencyBand             char(20)             not null
);

#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '025 - 050 MHz'
  WHERE (frequency_assigned >= '00000025.00000000' AND
         frequency_assigned <= '00000050.00000000')
;
#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '108 - 136 MHz'
  WHERE (frequency_assigned >= '00000108.00000000' AND
         frequency_assigned <= '00000136.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '150 - 174 MHz'
  WHERE (frequency_assigned >= '00000150.00000000' AND
         frequency_assigned <= '00000174.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '216 - 220 MHz'
  WHERE (frequency_assigned >= '00000216.00000000' AND
         frequency_assigned <= '00000220.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '450 - 512 MHz'
  WHERE (frequency_assigned >= '00000450.00000000' AND
         frequency_assigned <= '00000512.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '806 - 870 MHz'
  WHERE (frequency_assigned >= '00000806.00000000' AND
         frequency_assigned <= '00000870.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '896 - 940 MHz'
  WHERE (frequency_assigned >= '00000896.00000000' AND
         frequency_assigned <= '00000940.00000000')
;

#  delete frequencies that didn't fall into any of the above bands
DELETE
  FROM tblStatIQVTempFrequencyBand

  WHERE FrequencyBand = ''
;

#  count unique (radio) frequencies by county
CREATE TABLE tblStatIQVTempCount
  SELECT FrequencyBand, count( DISTINCT frequency_assigned ) AS CountOfUniqueFrequency_Assigned

  FROM tblStatIQVTempFrequencyBand

  GROUP BY FrequencyBand
;

#  derive squared proportions for the frequency distributions (part 1 of 2)
SELECT @SumOfCountOfUniqueFrequency_Assigned := sum( CountOfUniqueFrequency_Assigned )

FROM tblStatIQVTempCount
;

#  check it
CREATE TABLE tblStatIQVTempProportionCheck
  SELECT @SumOfCountOfUniqueFrequency_Assigned
;

#  derive squared proportions for the frequency distributions (part 2 of 2)
CREATE TABLE tblStatIQVTempProportion
  SELECT *, pow(( CountOfUniqueFrequency_Assigned / @SumOfCountOfUniqueFrequency_Assigned ), 2 )
         AS SquaredProportion

  FROM tblStatIQVTempCount
;

#  derive Index of Diversity (D)
SELECT @D := 1 - sum( SquaredProportion )

FROM tblStatIQVTempProportion
;

#  derive K
SELECT @CountOfUniqueFrequency:=count( FrequencyBand )

FROM tblStatIQVTempFrequency
;

#  check it
CREATE TABLE tblStatIQVTempKCheck
  SELECT @CountOfUniqueFrequency
;

#  derive Index of Qualitative Variation (IQV)
SET @IQV := ( @CountOfUniqueFrequency / ( @CountOfUniqueFrequency - 1 ))
               * @D * 100
;

#  record the value
INSERT INTO tblStatIQVFrequencyBandByCounty
  SET County = @County,
      IQV = @IQV
;


# Do a single County
SET @County := 'RIVERSIDE';

#  delete the table if it exists (it probably does)
DROP TABLE IF EXISTS tblStatIQVTempFrequencyBand;
DROP TABLE IF EXISTS tblStatIQVTempCount;
DROP TABLE IF EXISTS tblStatIQVTempProportion;
DROP TABLE IF EXISTS tblStatIQVTempProportionCheck;
DROP TABLE IF EXISTS tblStatIQVTempKCheck;


#  subselect the needed field for the specified County
CREATE TABLE tblStatIQVTempFrequencyBand
  SELECT DISTINCT frequency_assigned

  FROM tblGeoStateCA

  WHERE location_county = @County

  GROUP BY frequency_assigned
;

#  we'll need a new column to hold the Frequency Band
ALTER TABLE tblStatIQVTempFrequencyBand
  ADD COLUMN
(
      FrequencyBand             char(20)             not null
);

#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '025 - 050 MHz'
  WHERE (frequency_assigned >= '00000025.00000000' AND
         frequency_assigned <= '00000050.00000000')
;
#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '108 - 136 MHz'
  WHERE (frequency_assigned >= '00000108.00000000' AND
         frequency_assigned <= '00000136.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '150 - 174 MHz'
  WHERE (frequency_assigned >= '00000150.00000000' AND
         frequency_assigned <= '00000174.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '216 - 220 MHz'
  WHERE (frequency_assigned >= '00000216.00000000' AND
         frequency_assigned <= '00000220.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '450 - 512 MHz'
  WHERE (frequency_assigned >= '00000450.00000000' AND
         frequency_assigned <= '00000512.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '806 - 870 MHz'
  WHERE (frequency_assigned >= '00000806.00000000' AND
         frequency_assigned <= '00000870.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '896 - 940 MHz'
  WHERE (frequency_assigned >= '00000896.00000000' AND
         frequency_assigned <= '00000940.00000000')
;

#  delete frequencies that didn't fall into any of the above bands
DELETE
  FROM tblStatIQVTempFrequencyBand

  WHERE FrequencyBand = ''
;

#  count unique (radio) frequencies by county
CREATE TABLE tblStatIQVTempCount
  SELECT FrequencyBand, count( DISTINCT frequency_assigned ) AS CountOfUniqueFrequency_Assigned

  FROM tblStatIQVTempFrequencyBand

  GROUP BY FrequencyBand
;

#  derive squared proportions for the frequency distributions (part 1 of 2)
SELECT @SumOfCountOfUniqueFrequency_Assigned := sum( CountOfUniqueFrequency_Assigned )

FROM tblStatIQVTempCount
;

#  check it
CREATE TABLE tblStatIQVTempProportionCheck
  SELECT @SumOfCountOfUniqueFrequency_Assigned
;

#  derive squared proportions for the frequency distributions (part 2 of 2)
CREATE TABLE tblStatIQVTempProportion
  SELECT *, pow(( CountOfUniqueFrequency_Assigned / @SumOfCountOfUniqueFrequency_Assigned ), 2 )
         AS SquaredProportion

  FROM tblStatIQVTempCount
;

#  derive Index of Diversity (D)
SELECT @D := 1 - sum( SquaredProportion )

FROM tblStatIQVTempProportion
;

#  derive K
SELECT @CountOfUniqueFrequency:=count( FrequencyBand )

FROM tblStatIQVTempFrequency
;

#  check it
CREATE TABLE tblStatIQVTempKCheck
  SELECT @CountOfUniqueFrequency
;

#  derive Index of Qualitative Variation (IQV)
SET @IQV := ( @CountOfUniqueFrequency / ( @CountOfUniqueFrequency - 1 ))
               * @D * 100
;

#  record the value
INSERT INTO tblStatIQVFrequencyBandByCounty
  SET County = @County,
      IQV = @IQV
;


# Do a single County
SET @County := 'SAN BERNARDINO';

#  delete the table if it exists (it probably does)
DROP TABLE IF EXISTS tblStatIQVTempFrequencyBand;
DROP TABLE IF EXISTS tblStatIQVTempCount;
DROP TABLE IF EXISTS tblStatIQVTempProportion;
DROP TABLE IF EXISTS tblStatIQVTempProportionCheck;
DROP TABLE IF EXISTS tblStatIQVTempKCheck;


#  subselect the needed field for the specified County
CREATE TABLE tblStatIQVTempFrequencyBand
  SELECT DISTINCT frequency_assigned

  FROM tblGeoStateCA

  WHERE location_county = @County

  GROUP BY frequency_assigned
;

#  we'll need a new column to hold the Frequency Band
ALTER TABLE tblStatIQVTempFrequencyBand
  ADD COLUMN
(
      FrequencyBand             char(20)             not null
);

#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '025 - 050 MHz'
  WHERE (frequency_assigned >= '00000025.00000000' AND
         frequency_assigned <= '00000050.00000000')
;
#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '108 - 136 MHz'
  WHERE (frequency_assigned >= '00000108.00000000' AND
         frequency_assigned <= '00000136.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '150 - 174 MHz'
  WHERE (frequency_assigned >= '00000150.00000000' AND
         frequency_assigned <= '00000174.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '216 - 220 MHz'
  WHERE (frequency_assigned >= '00000216.00000000' AND
         frequency_assigned <= '00000220.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '450 - 512 MHz'
  WHERE (frequency_assigned >= '00000450.00000000' AND
         frequency_assigned <= '00000512.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '806 - 870 MHz'
  WHERE (frequency_assigned >= '00000806.00000000' AND
         frequency_assigned <= '00000870.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '896 - 940 MHz'
  WHERE (frequency_assigned >= '00000896.00000000' AND
         frequency_assigned <= '00000940.00000000')
;

#  delete frequencies that didn't fall into any of the above bands
DELETE
  FROM tblStatIQVTempFrequencyBand

  WHERE FrequencyBand = ''
;

#  count unique (radio) frequencies by county
CREATE TABLE tblStatIQVTempCount
  SELECT FrequencyBand, count( DISTINCT frequency_assigned ) AS CountOfUniqueFrequency_Assigned

  FROM tblStatIQVTempFrequencyBand

  GROUP BY FrequencyBand
;

#  derive squared proportions for the frequency distributions (part 1 of 2)
SELECT @SumOfCountOfUniqueFrequency_Assigned := sum( CountOfUniqueFrequency_Assigned )

FROM tblStatIQVTempCount
;

#  check it
CREATE TABLE tblStatIQVTempProportionCheck
  SELECT @SumOfCountOfUniqueFrequency_Assigned
;

#  derive squared proportions for the frequency distributions (part 2 of 2)
CREATE TABLE tblStatIQVTempProportion
  SELECT *, pow(( CountOfUniqueFrequency_Assigned / @SumOfCountOfUniqueFrequency_Assigned ), 2 )
         AS SquaredProportion

  FROM tblStatIQVTempCount
;

#  derive Index of Diversity (D)
SELECT @D := 1 - sum( SquaredProportion )

FROM tblStatIQVTempProportion
;

#  derive K
SELECT @CountOfUniqueFrequency:=count( FrequencyBand )

FROM tblStatIQVTempFrequency
;

#  check it
CREATE TABLE tblStatIQVTempKCheck
  SELECT @CountOfUniqueFrequency
;

#  derive Index of Qualitative Variation (IQV)
SET @IQV := ( @CountOfUniqueFrequency / ( @CountOfUniqueFrequency - 1 ))
               * @D * 100
;

#  record the value
INSERT INTO tblStatIQVFrequencyBandByCounty
  SET County = @County,
      IQV = @IQV
;


# Do a single County
SET @County := 'SAN DIEGO';

#  delete the table if it exists (it probably does)
DROP TABLE IF EXISTS tblStatIQVTempFrequencyBand;
DROP TABLE IF EXISTS tblStatIQVTempCount;
DROP TABLE IF EXISTS tblStatIQVTempProportion;
DROP TABLE IF EXISTS tblStatIQVTempProportionCheck;
DROP TABLE IF EXISTS tblStatIQVTempKCheck;


#  subselect the needed field for the specified County
CREATE TABLE tblStatIQVTempFrequencyBand
  SELECT DISTINCT frequency_assigned

  FROM tblGeoStateCA

  WHERE location_county = @County

  GROUP BY frequency_assigned
;

#  we'll need a new column to hold the Frequency Band
ALTER TABLE tblStatIQVTempFrequencyBand
  ADD COLUMN
(
      FrequencyBand             char(20)             not null
);

#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '025 - 050 MHz'
  WHERE (frequency_assigned >= '00000025.00000000' AND
         frequency_assigned <= '00000050.00000000')
;
#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '108 - 136 MHz'
  WHERE (frequency_assigned >= '00000108.00000000' AND
         frequency_assigned <= '00000136.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '150 - 174 MHz'
  WHERE (frequency_assigned >= '00000150.00000000' AND
         frequency_assigned <= '00000174.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '216 - 220 MHz'
  WHERE (frequency_assigned >= '00000216.00000000' AND
         frequency_assigned <= '00000220.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '450 - 512 MHz'
  WHERE (frequency_assigned >= '00000450.00000000' AND
         frequency_assigned <= '00000512.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '806 - 870 MHz'
  WHERE (frequency_assigned >= '00000806.00000000' AND
         frequency_assigned <= '00000870.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '896 - 940 MHz'
  WHERE (frequency_assigned >= '00000896.00000000' AND
         frequency_assigned <= '00000940.00000000')
;

#  delete frequencies that didn't fall into any of the above bands
DELETE
  FROM tblStatIQVTempFrequencyBand

  WHERE FrequencyBand = ''
;

#  count unique (radio) frequencies by county
CREATE TABLE tblStatIQVTempCount
  SELECT FrequencyBand, count( DISTINCT frequency_assigned ) AS CountOfUniqueFrequency_Assigned

  FROM tblStatIQVTempFrequencyBand

  GROUP BY FrequencyBand
;

#  derive squared proportions for the frequency distributions (part 1 of 2)
SELECT @SumOfCountOfUniqueFrequency_Assigned := sum( CountOfUniqueFrequency_Assigned )

FROM tblStatIQVTempCount
;

#  check it
CREATE TABLE tblStatIQVTempProportionCheck
  SELECT @SumOfCountOfUniqueFrequency_Assigned
;

#  derive squared proportions for the frequency distributions (part 2 of 2)
CREATE TABLE tblStatIQVTempProportion
  SELECT *, pow(( CountOfUniqueFrequency_Assigned / @SumOfCountOfUniqueFrequency_Assigned ), 2 )
         AS SquaredProportion

  FROM tblStatIQVTempCount
;

#  derive Index of Diversity (D)
SELECT @D := 1 - sum( SquaredProportion )

FROM tblStatIQVTempProportion
;

#  derive K
SELECT @CountOfUniqueFrequency:=count( FrequencyBand )

FROM tblStatIQVTempFrequency
;

#  check it
CREATE TABLE tblStatIQVTempKCheck
  SELECT @CountOfUniqueFrequency
;

#  derive Index of Qualitative Variation (IQV)
SET @IQV := ( @CountOfUniqueFrequency / ( @CountOfUniqueFrequency - 1 ))
               * @D * 100
;

#  record the value
INSERT INTO tblStatIQVFrequencyBandByCounty
  SET County = @County,
      IQV = @IQV
;


# Do a single County
SET @County := 'VENTURA';

#  delete the table if it exists (it probably does)
DROP TABLE IF EXISTS tblStatIQVTempFrequencyBand;
DROP TABLE IF EXISTS tblStatIQVTempCount;
DROP TABLE IF EXISTS tblStatIQVTempProportion;
DROP TABLE IF EXISTS tblStatIQVTempProportionCheck;
DROP TABLE IF EXISTS tblStatIQVTempKCheck;


#  subselect the needed field for the specified County
CREATE TABLE tblStatIQVTempFrequencyBand
  SELECT DISTINCT frequency_assigned

  FROM tblGeoStateCA

  WHERE location_county = @County

  GROUP BY frequency_assigned
;

#  we'll need a new column to hold the Frequency Band
ALTER TABLE tblStatIQVTempFrequencyBand
  ADD COLUMN
(
      FrequencyBand             char(20)             not null
);

#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '025 - 050 MHz'
  WHERE (frequency_assigned >= '00000025.00000000' AND
         frequency_assigned <= '00000050.00000000')
;
#  then we can assign the right frequencies to the right band
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '108 - 136 MHz'
  WHERE (frequency_assigned >= '00000108.00000000' AND
         frequency_assigned <= '00000136.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '150 - 174 MHz'
  WHERE (frequency_assigned >= '00000150.00000000' AND
         frequency_assigned <= '00000174.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '216 - 220 MHz'
  WHERE (frequency_assigned >= '00000216.00000000' AND
         frequency_assigned <= '00000220.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '450 - 512 MHz'
  WHERE (frequency_assigned >= '00000450.00000000' AND
         frequency_assigned <= '00000512.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '806 - 870 MHz'
  WHERE (frequency_assigned >= '00000806.00000000' AND
         frequency_assigned <= '00000870.00000000')
;
UPDATE tblStatIQVTempFrequencyBand
  SET FrequencyBand = '896 - 940 MHz'
  WHERE (frequency_assigned >= '00000896.00000000' AND
         frequency_assigned <= '00000940.00000000')
;

#  delete frequencies that didn't fall into any of the above bands
DELETE
  FROM tblStatIQVTempFrequencyBand

  WHERE FrequencyBand = ''
;

#  count unique (radio) frequencies by county
CREATE TABLE tblStatIQVTempCount
  SELECT FrequencyBand, count( DISTINCT frequency_assigned ) AS CountOfUniqueFrequency_Assigned

  FROM tblStatIQVTempFrequencyBand

  GROUP BY FrequencyBand
;

#  derive squared proportions for the frequency distributions (part 1 of 2)
SELECT @SumOfCountOfUniqueFrequency_Assigned := sum( CountOfUniqueFrequency_Assigned )

FROM tblStatIQVTempCount
;

#  check it
CREATE TABLE tblStatIQVTempProportionCheck
  SELECT @SumOfCountOfUniqueFrequency_Assigned
;

#  derive squared proportions for the frequency distributions (part 2 of 2)
CREATE TABLE tblStatIQVTempProportion
  SELECT *, pow(( CountOfUniqueFrequency_Assigned / @SumOfCountOfUniqueFrequency_Assigned ), 2 )
         AS SquaredProportion

  FROM tblStatIQVTempCount
;

#  derive Index of Diversity (D)
SELECT @D := 1 - sum( SquaredProportion )

FROM tblStatIQVTempProportion
;

#  derive K
SELECT @CountOfUniqueFrequency:=count( FrequencyBand )

FROM tblStatIQVTempFrequency
;

#  check it
CREATE TABLE tblStatIQVTempKCheck
  SELECT @CountOfUniqueFrequency
;

#  derive Index of Qualitative Variation (IQV)
SET @IQV := ( @CountOfUniqueFrequency / ( @CountOfUniqueFrequency - 1 ))
               * @D * 100
;

#  record the value
INSERT INTO tblStatIQVFrequencyBandByCounty
  SET County = @County,
      IQV = @IQV
;