-- ================================================================================
-- ================================================================================
-- 16-May-2006 EGallas
-- In the process of getting the column lengths, I discovered 'special' values:
-- negative values like -66, -67, -33, -34, and then  null values and 0's.
-- The Luminosity Group must decide how these will be dealt with.
-- Find them all and see if they have status_to records.
-- 
-- This file 060516_SpecialValues.sql
--
-- ================================================================================
-- ================================================================================

set echo on;
spool 060516_SpecialValues.out

-- ================================================================================
-- ================================================================================
-- I found 804 LBNs with duration longer than 100 seconds;
-- I found 807 LBNs with duration longer than 100 seconds
-- and 44 LBNs with duration longer than 1000 seconds
-- The max duration of 54867 seconds occurred very recently
-- for LBN 4766705 (on 30-May-2006 - 2 days ago ).
-- LBN report: lbns=4766705
-- This duration cannot be correct since this is 15 hours long
-- and the runs that include this LBN are no longer than 1 hour
--     Run 220779 .
--     Run 220780 .

set heading off;
select lbn,duration from lbns  where duration>100
	order by duration;

select lbn,duration from lbns  where duration>100
    order by LBN;

select avg(duration)  from lbns;

AVG(DURATION)
-------------
   49.2722897

select max(duration) from lbns;

MAX(DURATION)
-------------
        54867

select lbn,duration from lbns  where duration>1000 order by lbn;

       LBN   DURATION
---------- ----------
   1283140       1331
   1284749       1513
   1303731      11252
   1303733       5626
   1303736      11252
   1303738       5626
   1303744      11252
   1303746       5626
   1303750       5626
   1303753       5626
   1303760      50634
   1341530       2104
   1343416       2027
   1345136       2058
   1345894       4480
   1350278       1162
   1350298       1250
   1414904       3441
   1439895       5626
   1439896       5626
   1439900      45008
   1453898       1772
   1460787       1151
   1499208       1128
   1551614       1950
   1562492       2255
   1562724       1052
   1587354       2392
   1628201       1710
   1823080       2788
   1924262       1363
   1924476       4591
   1925128       4271
   1925155       1261
   2094171       4606
   3481446       1289
   3697119       1575
   3700081       1924
   3895945       1550
   3942536      15985
   4385044       3253
   4742493      41756
   4744232      23667
   4766705      54867

44 rows selected.


-- ================================================================================
-- ================================================================================
select count(*),DEL_LUM_NIM from DELIVERED_LUMS where 
	DEL_LUM_NIM is null or 
	DEL_LUM_NIM<0 or
	DEL_LUM_NIM=0
	GROUP BY DEL_LUM_NIM;
----  COUNT(*) DEL_LUM_NIM
-------------- -----------
----  33492764           0

select count(*),DEL_LUM_VME from DELIVERED_LUMS where 
	DEL_LUM_VME is null or 
	DEL_LUM_VME<0 or
	DEL_LUM_VME=0
	GROUP BY DEL_LUM_VME;
----  COUNT(*) DEL_LUM_VME
-------------- -----------
----     24775         -67
----  76645027           0
----      5920

select count(*),EXP_LUM_NIM from EXPOSED_LUMS where 
	EXP_LUM_NIM is null or 
	EXP_LUM_NIM<0 or
	EXP_LUM_NIM=0
	GROUP BY EXP_LUM_NIM;
----  COUNT(*) EXP_LUM_NIM
-------------- -----------
----  68107554           0

select count(*),EXP_LUM_VME from EXPOSED_LUMS where 
	EXP_LUM_VME is null or 
	EXP_LUM_VME<0 or
	EXP_LUM_VME=0
	GROUP BY EXP_LUM_VME;
----  COUNT(*) EXP_LUM_VME
-------------- -----------
---- 350817389         -45
----     30327

---- Is this ever filled ?
select count(*) from EXPOSED_LUMS where EXP_LUM_NIM is not null and EXP_LUM_NIM>0;
----	282740172
select count(*) from EXPOSED_LUMS where EXP_LUM_VME is not null and EXP_LUM_VME>0;
----    0

select count(*) from LBN_TRIGGERS where L1_LUM_NIM is not null and L1_LUM_NIM>0;
 214958384
select count(*),L1_LUM_NIM from LBN_TRIGGERS where 
	L1_LUM_NIM is null or 
	L1_LUM_NIM<0 or
	L1_LUM_NIM=0
	GROUP BY L1_LUM_NIM;
----  COUNT(*)  L1_LUM_NIM
-------------- ----------
----   6044044         -9
----  59836130          0

select count(*) from LBN_TRIGGERS where L1_LUM_VME is not null and L1_LUM_VME>0;
select count(*),L1_LUM_VME from LBN_TRIGGERS where 
	L1_LUM_VME is null or 
	L1_LUM_VME<0 or
	L1_LUM_VME=0
	GROUP BY L1_LUM_VME;
----  COUNT(*)  L1_LUM_VME
-------------- ----------
----   6043363        -19
---- 236384433          0
----     41405

select count(*) from LBN_TRIGGERS where REC_LUM_NIM is not null and REC_LUM_NIM>0;
select count(*),REC_LUM_NIM from LBN_TRIGGERS where 
	REC_LUM_NIM is null or 
	REC_LUM_NIM<0 or
	REC_LUM_NIM=0
	GROUP BY REC_LUM_NIM;
  COUNT(*) REC_LUM_NIM
---------- -----------
  78183977          -8
  36715241           0

select count(*) from LBN_TRIGGERS where REC_LUM_VME is not null and REC_LUM_VME>0;
select count(*),REC_LUM_VME from LBN_TRIGGERS where 
	REC_LUM_VME is null or 
	REC_LUM_VME<0 or
	REC_LUM_VME=0
	GROUP BY REC_LUM_VME;
----  COUNT(*) REC_LUM_VME                                                          
-------------- -----------                                                          
---- 245130890         -18                                                          
----   2020828           0                                                          
----     41405                                                                      

-- ================================================================================
-- ================================================================================
-- Do these special values have status_to entries ?
-- ================================================================================
-- ================================================================================
set linesize 256;

select count(*),DL.DEL_LUM_NIM,STE.CONDITION_ID,SEE.SHORT_DESC
	from DELIVERED_LUMS DL, STATUS_TO_ELTS STE, STATUS_EXPLAIN_ELTS SEE 
	where 
	DL.DEL_LUM_NIM<0 and
	DL.LBN = STE.LBN and
	STE.CONDITION_ID = SEE.CONDITION_ID
	GROUP BY DL.DEL_LUM_NIM,STE.CONDITION_ID,SEE.SHORT_DESC;
-- no rows since there are no special negative values

select count(*),DL.DEL_LUM_VME,STE.CONDITION_ID,SEE.SHORT_DESC
	from DELIVERED_LUMS DL, STATUS_TO_ELTS STE, STATUS_EXPLAIN_ELTS SEE 
	where 
	(DL.DEL_LUM_VME is null or DL.DEL_LUM_VME<0) and
	DL.LBN = STE.LBN and
	STE.CONDITION_ID = SEE.CONDITION_ID
	GROUP BY DL.DEL_LUM_VME,STE.CONDITION_ID,SEE.SHORT_DESC;
-- note: there are 
-- 24775 counts of -67 and
-- 5920  counts of Null
  COUNT(*) DEL_LUM_VME CONDITION_ID 	SHORT_DESC
    255041                        1	Exposure Fraction Unitarity Violation: 32bit Overflow
       851                        2	Exposure Fraction Underflow
    116069                        3	Exposure Fraction Unitarity Violation: LBN length
   7482799         -67            1	Exposure Fraction Unitarity Violation: 32bit Overflow
     52835         -67            2	Exposure Fraction Underflow
   1877111         -67            3	Exposure Fraction Unitarity Violation: LBN length

6 rows selected.

-- are there any LBNs where there are null or negative values where
-- there is no status_to flag
select count(*),DL.DEL_LUM_VME,STE.CONDITION_ID,SEE.SHORT_DESC
	from DELIVERED_LUMS DL, STATUS_TO_ELTS STE, STATUS_EXPLAIN_ELTS SEE 
	where 
	(DL.DEL_LUM_VME is null or DL.DEL_LUM_VME<0) and
	DL.LBN = STE.LBN and
	STE.CONDITION_ID = SEE.CONDITION_ID and
	NOT EXISTS
	(select STE2.LBN from STATUS_TO_ELTS STE2
	where STE2.LBN=STE.LBN)
	GROUP BY DL.DEL_LUM_VME,STE.CONDITION_ID,SEE.SHORT_DESC;

no rows selected

-- test the sql to see if it makes sense.
-- look for positive definite luminosity for LBNs with no status_to flag in STATUS_TO_ELTS

select count(*) from DELIVERED_LUMS DL 
	where (DL.DEL_LUM_VME > 0.) and
	NOT EXISTS (select STE2.LBN from STATUS_TO_ELTS STE2 where STE2.LBN=DL.LBN);
-- good, it found   14956729 DELIVERED_LUMS without a STATUS_TO_ELTS condition

