----- 2002 South San Francisco Bay GPS Height Modernization project
-----      2002-246 -> 2002-262)


------------------------------------------------------------------------
--- a. Verify start and stop of campaigns by first and last occupation.
---    Make sure they make sense before going further.
--- SELECT
select unique(stl.site_transaction_value)
	from site_transaction_log stl, site_affiliation sa
	where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
             and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_transaction_type = 'occupation'
              and stl.site_transaction_name = 'project name'
        order by stl.site_transaction_value ASC;
select *
	from site_transaction_log stl, site_affiliation sa
	where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
             and ((stl.effective_date < to_date('2002-246','YYYY-DDD'))
                  or (stl.effective_date > to_date('2002-263','YYYY-DDD')))
              and stl.site_transaction_type = 'occupation'
              and stl.site_transaction_name = 'project name'
              and stl.site_transaction_value = '2002 South San Francisco Bay GPS Height Mod'
        order by stl.site_transaction_value ASC;
--- UPDATE
update site_transaction_log set site_transaction_value = '2002 Yolo County GPS Subsidence'
       where
       site_transaction_log_id in (
	    select stl.site_transaction_log_id
            from site_transaction_log stl, site_affiliation sa
	    where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.site_transaction_type = 'occupation'
              and stl.site_transaction_name = 'project name'
	      and stl.site_transaction_value = '2002 Yolo County GPS Subsidence Project');
------------------------------------------------------------------------



--- b & c. Verify unique list of antenna models & serial numbers used in campaign.
---    Make sure they make sense before going further.
--- SELECT
select unique(stl.site_transaction_value)
	from site_transaction_log stl, site_affiliation sa
	where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_transaction_type = 'antenna / dome'
              and stl.site_transaction_name = 'antenna model code'
        order by stl.site_transaction_value ASC;
--- SELECT
select stl.site_transaction_value,count(stl.rowid)
	from site_transaction_log stl, site_affiliation sa
	where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_transaction_type = 'antenna / dome'
              and stl.site_transaction_name = 'antenna serial number'
        group by stl.site_transaction_value
        order by stl.site_transaction_value ASC;
--- UPDATE
update site_transaction_log set site_transaction_value = '0220123980'
       where
       site_transaction_log_id in (
	    select stl.site_transaction_log_id
            from site_transaction_log stl, site_affiliation sa
	    where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_transaction_type = 'antenna / dome'
              and stl.site_transaction_name = 'antenna serial number'
	      and stl.site_transaction_value = '0220125980');



------------------------------------------------------------------------



--- d & e. Verify unique list of receiver models & serial numbers used in campaign.
---    Make sure they make sense before going further.
--- SELECT
select unique(stl.site_transaction_value)
	from site_transaction_log stl, site_affiliation sa
	where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_transaction_type = 'receiver'
              and stl.site_transaction_name = 'model code'
        order by stl.site_transaction_value ASC;
--- SELECT
select stl.site_transaction_value,count(stl.rowid)
	from site_transaction_log stl, site_affiliation sa
	where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_transaction_type = 'receiver'
              and stl.site_transaction_name = 'serial number'
        group by stl.site_transaction_value
        order by stl.site_transaction_value ASC;
--- UPDATE
update site_transaction_log set site_transaction_value = '0220178520'
       where
       site_transaction_log_id in (
	    select stl.site_transaction_log_id
            from site_transaction_log stl, site_affiliation sa
	    where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_transaction_type = 'receiver'
              and stl.site_transaction_name = 'serial number'
	      and stl.site_transaction_value = '02201792520');




------------------------------------------------------------------------



--- f. Verify that occupation start/stop times make sense...ie. no
---    occurrences of start-start or stop-stop.
---
--- SELECT
select stl.site_id,to_char(stl.effective_date,'YYYY-DDD'),
       stl.site_transaction_value,stl.site_transaction_log_id
	from site_transaction_log stl, site_affiliation sa
	where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_transaction_type = 'receiver'
              and stl.site_transaction_name = 'serial number'
        order by stl.site_id ASC,
	         stl.effective_date ASC;

select stl.site_id,to_char(stl.effective_date,'YYYY-DDD'),
       stl.site_transaction_value,stl.site_transaction_log_id
	from site_transaction_log stl, site_affiliation sa
	where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_transaction_type = 'antenna / dome'
              and stl.site_transaction_name = 'antenna serial number'
            --  and stl.site_id = '16990000'
        order by stl.site_id ASC,
	         stl.effective_date ASC;

select stl.site_id,stl.site_transaction_type,
       stl.site_transaction_name,
       to_char(stl.effective_date,'YYYY-DDD:HH24:MI'),
       stl.site_transaction_value,stl.site_transaction_log_id
	from site_transaction_log stl, site_affiliation sa
	where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_id = 'g1100000'
        order by stl.site_id ASC,
                 stl.site_transaction_type ASC,
                 stl.site_transaction_name ASC,
	         stl.effective_date ASC;



------------------------------------------------------------------------



--- G.  Acquire number of unique values per transaction type/name
---
--- G1.
set pagesize 1000
column site_transaction_value format a30 trunc
select stl.site_transaction_type,stl.site_transaction_name,count(unique(stl.site_transaction_value))
	from site_transaction_log stl, site_affiliation sa
	where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
        group by stl.site_transaction_type,stl.site_transaction_name
        order by stl.site_transaction_type,stl.site_transaction_name;
--- G2.
select stl.site_transaction_value,count(stl.rowid)
	from site_transaction_log stl, site_affiliation sa
	where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_transaction_type = 'occupation'
              and stl.site_transaction_name = 'observer'
        group by stl.site_transaction_value
        order by stl.site_transaction_value ASC;
--- G3.
update site_transaction_log set site_transaction_value = ' ' where
       site_transaction_log_id in (
	    select stl.site_transaction_log_id
            from site_transaction_log stl, site_affiliation sa
	    where stl.site_id = sa.site_id and sa.array_code = 'CALPAN'
              and stl.effective_date > to_date('2002-245','YYYY-DDD')
              and stl.effective_date < to_date('2002-263','YYYY-DDD')
              and stl.site_transaction_type = 'occupation'
              and stl.site_transaction_name = 'project name'
	      and stl.site_transaction_value = 'n/a');

