-- ================================================================================
-- E. Gallas
-- May, 2007 
-- Designed to run after the columns are renamed (default and _OLD columns exist)
--
-- 1. Make sure special values are equal
--
-- 2. Make sure new luminosity is always > old luminosity
--
-- 3. See if the sum over ticks is the same as tick0 
--    These queries have been moved to 
-- 
-- ================================================================================
-- ================================================================================
spool Final_check.out;
set echo on;
set linesize 100;

desc DELIVERED_LUMS;

desc EXPOSED_LUMS;

desc LBN_TRIGGERS;

-- ================================================================================
-- ================================================================================
-- 1. Make sure special values are equal

SELECT LOCALTIMESTAMP FROM DUAL; 

select LBN,TICK_NUM,DEL_LUM_NIM_OLD,DEL_LUM_NIM,DEL_LUM_NIM-DEL_LUM_NIM_OLD
	from DELIVERED_LUMS
	where DEL_LUM_NIM!=DEL_LUM_NIM_OLD and
              (DEL_LUM_NIM_OLD < 0. or DEL_LUM_NIM_OLD is NULL or DEL_LUM_NIM_OLD = 0.);
SELECT LOCALTIMESTAMP FROM DUAL; 
select LBN,TICK_NUM,DEL_LUM_VME_OLD,DEL_LUM_VME,DEL_LUM_VME-DEL_LUM_VME_OLD
	from DELIVERED_LUMS
	where DEL_LUM_VME!=DEL_LUM_VME_OLD and
              (DEL_LUM_VME_OLD < 0. or DEL_LUM_VME_OLD is NULL or DEL_LUM_VME_OLD = 0.);

SELECT LOCALTIMESTAMP FROM DUAL; 

select LBN,TICK_NUM,EXP_LUM_NIM_OLD,EXP_LUM_NIM,EXP_LUM_NIM-EXP_LUM_NIM_OLD
	from EXPOSED_LUMS
	where EXP_LUM_NIM!=EXP_LUM_NIM_OLD and
              (EXP_LUM_NIM_OLD < 0. or EXP_LUM_NIM_OLD is NULL or EXP_LUM_NIM_OLD = 0.);
SELECT LOCALTIMESTAMP FROM DUAL; 
select LBN,TICK_NUM,EXP_LUM_VME_OLD,EXP_LUM_VME,EXP_LUM_VME-EXP_LUM_VME_OLD
	from EXPOSED_LUMS
	where EXP_LUM_VME!=EXP_LUM_VME_OLD and
              (EXP_LUM_VME_OLD < 0. or EXP_LUM_VME_OLD is NULL or EXP_LUM_VME_OLD = 0.);

SELECT LOCALTIMESTAMP FROM DUAL; 

select LBN,L1_LUM_NIM_OLD,L1_LUM_NIM
	from LBN_TRIGGERS
	where L1_LUM_NIM!=L1_LUM_NIM_OLD and
              (L1_LUM_NIM_OLD < 0. or L1_LUM_NIM_OLD is NULL or L1_LUM_NIM_OLD = 0.);
SELECT LOCALTIMESTAMP FROM DUAL; 
select LBN,L1_LUM_VME_OLD,L1_LUM_VME
	from LBN_TRIGGERS
	where L1_LUM_VME!=L1_LUM_VME_OLD and
              (L1_LUM_VME_OLD < 0. or L1_LUM_VME_OLD is NULL or L1_LUM_VME_OLD = 0.);
SELECT LOCALTIMESTAMP FROM DUAL; 
select LBN,REC_LUM_NIM_OLD,REC_LUM_NIM
	from LBN_TRIGGERS
	where REC_LUM_NIM!=REC_LUM_NIM_OLD and
              (REC_LUM_NIM_OLD < 0. or REC_LUM_NIM_OLD is NULL or REC_LUM_NIM_OLD = 0.);
SELECT LOCALTIMESTAMP FROM DUAL; 
select LBN,REC_LUM_VME_OLD,REC_LUM_VME
	from LBN_TRIGGERS
	where REC_LUM_VME!=REC_LUM_VME_OLD and
              (REC_LUM_VME_OLD < 0. or REC_LUM_VME_OLD is NULL or REC_LUM_VME_OLD = 0.);
SELECT LOCALTIMESTAMP FROM DUAL; 

-- ================================================================================
-- ================================================================================
-- 2. Make sure new luminosity is always > old luminosity

SELECT LOCALTIMESTAMP FROM DUAL; 

select count(*)
	from DELIVERED_LUMS
	where DEL_LUM_NIM-DEL_LUM_NIM_OLD<0. and
              DEL_LUM_NIM_OLD is not null and
              DEL_LUM_NIM_OLD > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 
select count(*)
	from DELIVERED_LUMS
	where DEL_LUM_VME-DEL_LUM_VME_OLD<0. and
              DEL_LUM_VME_OLD is not null and
              DEL_LUM_VME_OLD > 0;

SELECT LOCALTIMESTAMP FROM DUAL; 

select count(*)
	from EXPOSED_LUMS
	where EXP_LUM_NIM-EXP_LUM_NIM_OLD<0. and
              EXP_LUM_NIM_OLD is not null and
              EXP_LUM_NIM_OLD > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 
select count(*)
	from EXPOSED_LUMS
	where EXP_LUM_VME-EXP_LUM_VME_OLD<0. and
              EXP_LUM_VME_OLD is not null and
              EXP_LUM_VME_OLD > 0;

SELECT LOCALTIMESTAMP FROM DUAL; 

select count(*)
	from LBN_TRIGGERS
	where L1_LUM_NIM-L1_LUM_NIM_OLD<0. and
              L1_LUM_NIM_OLD is not null and
              L1_LUM_NIM_OLD > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 
select count(*)
	from LBN_TRIGGERS
	where L1_LUM_VME-L1_LUM_VME_OLD<0. and
              L1_LUM_VME_OLD is not null and
              L1_LUM_VME_OLD > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 
select count(*)
	from LBN_TRIGGERS
	where REC_LUM_NIM-REC_LUM_NIM_OLD<0. and
              REC_LUM_NIM_OLD is not null and
              REC_LUM_NIM_OLD > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 
select count(*)
	from LBN_TRIGGERS
	where REC_LUM_VME-REC_LUM_VME_OLD<0. and
              REC_LUM_VME_OLD is not null and
              REC_LUM_VME_OLD > 0;
SELECT LOCALTIMESTAMP FROM DUAL; 

-- ============
-- if there were any matches (initially I had some, 
-- I'd want to know what period they were in
-- so I wrote this query including use of the LumPeriodTable1 Table.

 desc LumPeriodTable1;

--  Name          Type
--  ------------- ----------------------------
--  PERIOD        VARCHAR2(10)
--  LBN_LO        NUMBER(38)
--  LBN_HI        NUMBER(38)

select count(*) from LumPeriodTable1;
select * from LumPeriodTable1;

SELECT LOCALTIMESTAMP FROM DUAL; 

select LPT.PERIOD,DL.LBN,DL.TICK_NUM,DL.DEL_LUM_NIM_OLD,DL.DEL_LUM_NIM,DL.DEL_LUM_NIM-DL.DEL_LUM_NIM_OLD
	from DELIVERED_LUMS DL, LumPeriodTable1 LPT
	where DL.DEL_LUM_NIM-DL.DEL_LUM_NIM_OLD<0. and
              DL.DEL_LUM_NIM_OLD is not null and
              DL.DEL_LUM_NIM_OLD > 0 and
		DL.LBN>=LPT.LBN_LO and DL.LBN<=LPT.LBN_HI
	order by LPT.PERIOD,DL.DEL_LUM_NIM-DL.DEL_LUM_NIM_OLD desc;
SELECT LOCALTIMESTAMP FROM DUAL; 
select LPT.PERIOD,count(*)
	from DELIVERED_LUMS DL, LumPeriodTable1 LPT
	where DL.DEL_LUM_NIM-DL.DEL_LUM_NIM_OLD<0. and
              DL.DEL_LUM_NIM_OLD is not null and
              DL.DEL_LUM_NIM_OLD > 0 and
		DL.LBN>=LPT.LBN_LO and DL.LBN<=LPT.LBN_HI
	group by LPT.period;

-- ================================================================================
-- ================================================================================
-- 3. See if the sum over ticks is the same as tick0
-- Thanks to Anil Kumar for initial help writing this SQL.

-- This SQL was moved to 070604_Sum_Check* files

SELECT LOCALTIMESTAMP FROM DUAL; 

spool off;
