-- =========================================================
-- =========================================================
--
-- E. Gallas 
-- June 2007
-- Intended to run AFTER the rename (no _NEW columns)
--
-- count the number of records with tick0!=sumTicks and 
-- other various criteria
--
-- =========================================================
-- =========================================================
set echo on;
spool 070604_Sum_Check2.out
set pagesize 100;
column diff format 99999.999999;
-- =========================================================
-- =========================================================
-- count all LBNs with tick0!=sumTicks
-- =========================================================
-- DL NIM 
select count(*)	from 
	(select LBN, sum(DEL_LUM_NIM) SUM_DEL_LUM_NIM
	 from delivered_lums 
	 where TICK_NUM <> 0
	 group by LBN) 		s, 
	(select LBN,DEL_LUM_NIM
	 from delivered_lums
	 where TICK_NUM = 0 ) t
where  t.LBN = s.LBN
and abs(t.DEL_LUM_NIM-SUM_DEL_LUM_NIM) > 0.000005;

-- DL VME 
-- count all LBNs with DL VME tick0!=sumTicks
select count(*)	from 
	(select LBN, sum(DEL_LUM_VME) SUM_DEL_LUM_VME
	 from delivered_lums 
	 where TICK_NUM <> 0
	 group by LBN) 		s, 
	(select LBN,DEL_LUM_VME
	 from delivered_lums
	 where TICK_NUM = 0 ) t
where  t.LBN = s.LBN
and abs(t.DEL_LUM_VME-SUM_DEL_LUM_VME) > 0.000005;

-- EL NIM (must add EG_NUM)
select count(*)	from 
	(select LBN,EG_NUM, sum(EXP_LUM_NIM) SUM_EXP_LUM_NIM
	 from exposed_lums
	 where TICK_NUM <> 0
	 group by LBN,EG_NUM) 		s, 
	(select LBN,EG_NUM,EXP_LUM_NIM
	 from exposed_lums
	 where TICK_NUM = 0 ) t
where  t.LBN    = s.LBN
and    t.EG_NUM = s.EG_NUM
and abs(t.EXP_LUM_NIM-SUM_EXP_LUM_NIM) > 0.000005;

-- EL VME
select count(*) from 
	(select LBN,EG_NUM, sum(EXP_LUM_NIM) SUM_EXP_LUM_NIM
	 from exposed_lums
	 where TICK_NUM <> 0
	 group by LBN,EG_NUM) 		s, 
	(select LBN,EG_NUM,EXP_LUM_NIM
	 from exposed_lums
	 where TICK_NUM = 0 ) t
where  t.LBN    = s.LBN
and    t.EG_NUM = s.EG_NUM
and abs(t.EXP_LUM_NIM-SUM_EXP_LUM_NIM) > 0.000005;

-- =========================================================
-- =========================================================
-- if a non-zero count is found above, 
--      see how many have lum>0.
--      see how many have lum=0.
-- =========================================================
-- DL VME 
select count(*)	from 
	(select LBN, sum(DEL_LUM_VME) SUM_DEL_LUM_VME
	 from delivered_lums 
	 where TICK_NUM <> 0
	 group by LBN) 		s, 
	(select LBN,DEL_LUM_VME
	 from delivered_lums
	 where TICK_NUM = 0 
	 and DEL_LUM_VME>0. ) t
where  t.LBN = s.LBN
and abs(t.DEL_LUM_VME-SUM_DEL_LUM_VME) > 0.000005;

-- on lumd: 1110 rows

select count(*)	from 
	(select LBN, sum(DEL_LUM_VME) SUM_DEL_LUM_VME
	 from delivered_lums 
	 where TICK_NUM <> 0
	 group by LBN) 		s, 
	(select LBN,DEL_LUM_VME
	 from delivered_lums
	 where TICK_NUM = 0 
	 and DEL_LUM_VME=0. ) t
where  t.LBN = s.LBN
and abs(t.DEL_LUM_VME-SUM_DEL_LUM_VME) > 0.000005;

-- on lumd: 44568 rows

-- =========================================================
-- =========================================================
-- add the store number and period for lum>0.
-- =========================================================

select t.LBN, 
	(t.DEL_LUM_VME-SUM_DEL_LUM_VME) diff, 
	t.DEL_LUM_VME,
	s.SUM_DEL_LUM_VME, 
       	tr1.pvalue store,
       	lpt1.period PERIOD
from 
        TRANSITION_RECORDS tr1,
        TRANSITION_RECORDS tr2,
        LumPeriodTable1 lpt1,
	(select LBN, sum(DEL_LUM_VME) SUM_DEL_LUM_VME
	 from delivered_lums
	 where TICK_NUM <> 0
	 group by LBN) 		s, 
	(select LBN,DEL_LUM_VME
	 from delivered_lums
	 where TICK_NUM = 0
	 and DEL_LUM_VME>0. ) t
where   t.LBN = s.LBN
and    (t.LBN >= lpt1.lbn_lo and t.LBN<=lpt1.lbn_hi)
and    (t.LBN >= tr1.LBN and tr1.pname='beginStore')
and    (t.LBN <= tr2.LBN and tr2.pname='endStore')
and    (tr1.pvalue = tr2.pvalue and tr1.pvalue != -666)
and abs(t.DEL_LUM_VME-SUM_DEL_LUM_VME) > 0.000005
order by t.LBN;
