-- Stars with multiple measurements
-- remove cone search constraint for a full scan

SELECT d.objID, filterType =
      CASE d.filterID
         WHEN 1 THEN 'g'
         WHEN 2 THEN 'r'
         WHEN 3 THEN 'i'
         WHEN 4 THEN 'z'
         WHEN 5 THEN 'y'
      END,
	count(*) n, -- counts the number of detections
	max(calcMag) calcMagMax, -- computes max magnitude
	min(calcMag) calcMagMin  -- computes min magnitude
FROM P2Detection d JOIN STAR s 
	ON d.objID = s.objID -- detections associated to the object
	-- for a full scan, remove the Cone Search constraint
	JOIN dbo.fGetNearbyObjEq(220.30, 0.5, 60) n -- search on 1 degree
		ON s.objID = n.objID  -- only stars
GROUP BY d.objID, d.filterID  -- groups by object and filter
HAVING   count(*)> 1 
	AND (max(calcMag) - min(calcMag))> 0.1 -- variation threshold 
ORDER BY objID, d.filterID -- order the results for presentation purposes

