-- ==========================================================================
-- Stars with Multiple Epoch Measurements
-- Provide a list of stars with multiple epoch measurements in a given filter, 
-- which have light variations > 0.1 magnitude.
-- 
-- Scan for stars that have a multiple detections (observed at a different times)
-- with a predicate for the light variations.

SELECT 
    d.objID, s.ra, s.dec, s.gmag, d.calcMag,
	abs(s.gmag - d.calcMag) gVariation, 
--	s.rmag,  abs(s.rmag-d.calcMag) rVariation,
--	s.imag,  abs(s.imag-d.calcMag) iVariation, 
--	s.zmag,  abs(s.zmag-d.calcMag) zVariation, 
--	s.ymag,  abs(s.ymag-d.calcMag) yVariation, 
	d.detectID,  d.obsTime
FROM Star s -- object 
	-- for a full scan, remove the Cone Search constraint
  JOIN dbo.fGetNearbyObjEq(220.30,	0.5, 60) n		-- 1 degree
  ON s.objId = n.objId	-- to get galaxy object params
  JOIN P2Detection d  ON s.objId = d.objID -- to get detection params
  JOIN Filters f	  ON (f.filterID = d.filterID AND 
						  f.filterType = 'g'	
--						  f.filterType = 'r'	
--						  f.filterType = 'i'	
--						  f.filterType = 'z'	
--						  f.filterType = 'y'
						 )	
WHERE 
    s.gMag BETWEEN 1 AND 27	-- object magnitudes are reasonable in all filters
    AND s.rMag BETWEEN 1 AND 27 
    AND s.iMag BETWEEN 1 AND 27 
    AND s.zMag BETWEEN 1 AND 27 
    AND s.yMag BETWEEN 1 AND 27 
	AND (d.calcMag between 1 and f.limMag) -- detection magnitudes are reasonble
    AND ( -- and there is a magnitude variation above the threshold. 
  		abs(s.gmag - d.calcMag) > (.1 + s.gBarErr + abs(d.calcMagErr)) 
--		abs(s.rmag - d.calcMag) > (.1 + s.rBarErr + abs(d.calcMagErr)) 
-- 		abs(s.imag - d.calcMag) > (.1 + s.iBarErr + abs(d.calcMagErr)) 
--		abs(s.zmag - d.calcMag) > (.1 + s.zBarErr + abs(d.calcMagErr)) 
-- 		abs(s.ymag - d.calcMag) > (.1 + s.yBarErr + abs(d.calcMagErr)) 
    ) 
ORDER by d.objid, d.detectID