----- 2002 Yolo County project (2002-180 -> 2002-220)


--- Verify unique list of antenna models
---
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-180','YYYY-DDD')
              and stl.effective_date < to_date('2002-220','YYYY-DDD')
              and stl.site_transaction_type = 'antenna / dome'
              and stl.site_transaction_name = 'antenna model code'
        order by stl.site_transaction_value ASC;

--- Verify unique list of antenna serial numbers
---
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-180','YYYY-DDD')
              and stl.effective_date < to_date('2002-220','YYYY-DDD')
              and stl.site_transaction_type = 'antenna / dome'
              and stl.site_transaction_name = 'antenna serial number'
        order by stl.site_transaction_value ASC;


--- Verify unique list of receiver models
---
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-180','YYYY-DDD')
              and stl.effective_date < to_date('2002-220','YYYY-DDD')
              and stl.site_transaction_type = 'receiver'
              and stl.site_transaction_name = 'model code'
        order by stl.site_transaction_value ASC;

--- Verify unique list of receiver serial numbers
---
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-180','YYYY-DDD')
              and stl.effective_date < to_date('2002-220','YYYY-DDD')
              and stl.site_transaction_type = 'receiver'
              and stl.site_transaction_name = 'serial number'
        order by stl.site_transaction_value ASC;



--- Verify occupation start-stop ordering by site
---
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-180','YYYY-DDD')
              and stl.effective_date < to_date('2002-220','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-180','YYYY-DDD')
              and stl.effective_date < to_date('2002-220','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-180','YYYY-DDD')
              and stl.effective_date < to_date('2002-220','YYYY-DDD')
              and stl.site_id = 'russ0000'
        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.
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-180','YYYY-DDD')
              and stl.effective_date < to_date('2002-220','YYYY-DDD')
        group by stl.site_transaction_type,stl.site_transaction_name
        order by stl.site_transaction_type,stl.site_transaction_name;
--- G2.
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-180','YYYY-DDD')
              and stl.effective_date < to_date('2002-220','YYYY-DDD')
              and stl.site_transaction_type = 'occupation'
              and stl.site_transaction_name = 'observer'
        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-180','YYYY-DDD')
              and stl.effective_date < to_date('2002-220','YYYY-DDD')
              and stl.site_transaction_type = 'occupation'
              and stl.site_transaction_name = 'project name'
	      and stl.site_transaction_value = 'n/a');

