-- ================================================================================
-- E. Gallas
-- June, 2007
-- 
-- see 070604_Sum_Check.notes for notes on these queries.
-- ================================================================================

set echo on;
set pagesize 100;
column diff format 99999.999999;

SELECT LOCALTIMESTAMP FROM DUAL; 
-- ================================================================================
-- original query resulting in 2935 records found
--
-- problems: not sorted by lbn, 
--           frequent exact multiples of 67.00
--           need store number
--           need period
/* 
select t.LBN, (t.DEL_LUM_VME-SUM_DEL_LUM_VME) diff, t.DEL_LUM_VME
	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.00000005;
*/
SELECT LOCALTIMESTAMP FROM DUAL; 
-- ================================================================================
-- add the count and group by diff and use > 0.000005;
-- I can't put count(*)>=2 in the where clause but I can order by count
--
-- problems: Count is always 1, still have frequent exact multiples of 67.00
--           not sorted by lbn, 
--           frequent exact multiples of 67.00
--           need store number
--           need period
/*
select count(*), (t.DEL_LUM_VME-SUM_DEL_LUM_VME) diff
	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
-- and count(*)>=2
group by (t.DEL_LUM_VME-SUM_DEL_LUM_VME)
order by count(*);
*/
SELECT LOCALTIMESTAMP FROM DUAL; 
-- ================================================================================
-- see if I can exclude the integer multiples of 67.00 diffs
-- and order by LBN
--
-- problems: Still have frequent exact multiples of 67.00
/*
select t.LBN, (t.DEL_LUM_VME-SUM_DEL_LUM_VME) diff, t.DEL_LUM_VME
	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
and abs(t.DEL_LUM_VME-SUM_DEL_LUM_VME) not in 
   (67.00,  2*67.00,  3*67.00,  4*67.00,  5*67.00, 
  6*67.00,  7*67.00,  8*67.00,  9*67.00, 10*67.00,
 11*67.00, 12*67.00, 13*67.00, 14*67.00, 15*67.00, 
 16*67.00, 17*67.00, 18*67.00, 19*67.00, 20*67.00,
 21*67.00, 22*67.00, 23*67.00, 24*67.00, 25*67.00, 
 26*67.00, 27*67.00, 28*67.00, 29*67.00, 30*67.00,
 31*67.00, 32*67.00, 33*67.00, 34*67.00, 35*67.00, 36*67.00)
order by t.LBN;
*/
SELECT LOCALTIMESTAMP FROM DUAL; 
-- ================================================================================
-- Add the store number
/*
select t.LBN, 
	(t.DEL_LUM_VME-SUM_DEL_LUM_VME) diff, 
	t.DEL_LUM_VME,
       tr1.pvalue store
	from 
        TRANSITION_RECORDS tr1,
        TRANSITION_RECORDS tr2,
	(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 >= 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
and abs(t.DEL_LUM_VME-SUM_DEL_LUM_VME) not in 
   (67.00,  2*67.00,  3*67.00,  4*67.00,  5*67.00, 
  6*67.00,  7*67.00,  8*67.00,  9*67.00, 10*67.00,
 11*67.00, 12*67.00, 13*67.00, 14*67.00, 15*67.00, 
 16*67.00, 17*67.00, 18*67.00, 19*67.00, 20*67.00,
 21*67.00, 22*67.00, 23*67.00, 24*67.00, 25*67.00, 
 26*67.00, 27*67.00, 28*67.00, 29*67.00, 30*67.00,
 31*67.00, 32*67.00, 33*67.00, 34*67.00, 35*67.00, 36*67.00)
order by t.LBN;
*/
SELECT LOCALTIMESTAMP FROM DUAL; 
-- ================================================================================
-- Add the period
/*
select t.LBN, 
	(t.DEL_LUM_VME-SUM_DEL_LUM_VME) diff, 
	t.DEL_LUM_VME,
       lpt1.period PERIOD
	from 
        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 abs(t.DEL_LUM_VME-SUM_DEL_LUM_VME) > 0.000005
and abs(t.DEL_LUM_VME-SUM_DEL_LUM_VME) not in 
   (67.00,  2*67.00,  3*67.00,  4*67.00,  5*67.00, 
  6*67.00,  7*67.00,  8*67.00,  9*67.00, 10*67.00,
 11*67.00, 12*67.00, 13*67.00, 14*67.00, 15*67.00, 
 16*67.00, 17*67.00, 18*67.00, 19*67.00, 20*67.00,
 21*67.00, 22*67.00, 23*67.00, 24*67.00, 25*67.00, 
 26*67.00, 27*67.00, 28*67.00, 29*67.00, 30*67.00,
 31*67.00, 32*67.00, 33*67.00, 34*67.00, 35*67.00, 36*67.00)
order by t.LBN;
*/
SELECT LOCALTIMESTAMP FROM DUAL; 
-- ================================================================================
-- Add both the store number and the period
-- remove the not working IN clause for int multiples of 67

select t.LBN, 
	(t.DEL_LUM_VME-SUM_DEL_LUM_VME) diff, 
	t.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>=5308180 and t.lbn<=5451508)
and 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;

SELECT LOCALTIMESTAMP FROM DUAL; 
-- ================================================================================
-- test for an lbn ranges:  where  (t.lbn>=5308180 and t.lbn<=5451508)
--                          known to have integer diffs and others in store
--
-- found 
-- INT(diff) does not work -- ORA-00904: "INT": invalid identifier
--
-- These TO_CHARs take too long:
-- try TO_CHAR() like '%.000000%'
-- and TO_CHAR() not like '%.000000%'
--
-- MOD(x,y)	Remainder of x divided by y
-- round(X)	Returns the argument X, rounded to an integer.

select t.LBN, 
	(t.DEL_LUM_VME-SUM_DEL_LUM_VME) diff, 
	t.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>=5308180 and t.lbn<=5451508)
and 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
-- and to_char(t.DEL_LUM_VME-SUM_DEL_LUM_VME) like '%.000000%'
--
-- and to_char(t.DEL_LUM_VME-SUM_DEL_LUM_VME) not like '%.000000%'
--
and abs( (t.DEL_LUM_VME-SUM_DEL_LUM_VME) - 
    round(t.DEL_LUM_VME-SUM_DEL_LUM_VME)) <= 0.000005
order by t.LBN;

--- opposite round off clause

select t.LBN, 
	(t.DEL_LUM_VME-SUM_DEL_LUM_VME) diff, 
	t.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>=5308180 and t.lbn<=5451508)
and 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
-- and to_char(t.DEL_LUM_VME-SUM_DEL_LUM_VME) like '%.000000%'
--
-- and to_char(t.DEL_LUM_VME-SUM_DEL_LUM_VME) not like '%.000000%'
--
and abs( (t.DEL_LUM_VME-SUM_DEL_LUM_VME) - 
    round(t.DEL_LUM_VME-SUM_DEL_LUM_VME)) >= 0.000005
order by t.LBN;



