
-- Get individual RVs from individual visits, the ASPCAP parameters for the
-- combined spectra for stars which have more than 6 visits:
-- Each star is visited several times, and in some case many times, in
-- order to build up signal-to-noise and to detect radial velocity
-- variations. The information about each visit to each star is in the
-- apogeeVisit table. One could join this table with apogeeStar on
-- apogee_id in order to literally find all visits to each star. However,
-- in this example we are interested in just finding those visits that
-- actually contributed to each combined spectrum. In this case, bad visits
-- are excluded and commissioning data and survey data are kept separate
-- (not combined). To find these stars, one may use the apogeeStarVisit
-- table in CAS, or the array visit_pk which exists for each star in the
-- allStar file. Alternatively, if you wanted to find all visits to a
-- particular star, one could replace in the code below apogeeStarVisit
-- with apogeeStarAllVisit and visit_pk with all_visit_pk.

SELECT top 100
visit.*, aspcap.teff, aspcap.logg, aspcap.metals
FROM apogeeVisit visit
   JOIN apogeeStarVisit starvisit on visit.visit_id = starvisit.visit_id
   JOIN aspcapStar aspcap on aspcap.apstar_id = starvisit.apstar_id
   JOIN apogeeStar star on star.apstar_id = starvisit.apstar_id
WHERE (aspcap.aspcapflag & dbo.fApogeeAspcapFlag('STAR_BAD')) = 0 and aspcap.teff > 0
    and (star.apogee_target1 & dbo.fApogeeTarget1('APOGEE_LONG')) > 0
    and star.nvisits > 6 
ORDER BY visit.apogee_id
