#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7

    Select data between subquery dates


    I have an athletics participation table that only has the relevant emplid and effective date field. There is no term field on the table. I'm trying to only select those emplid's where the max( effdt) for the emplid is between the begin and end date of the current term. I only want to select current athletes. I would much rather it be for the current academic year but it seems impossible. Why can't I use max(effdt) here?
    sql Code:
     
     AND h.emplid IN(SELECT b.emplid FROM PS_ATHL_PART_STAT b
       WHERE MAX(b.effdt) BETWEEN (SELECT term_begin_date AND term_end_date
    									FROM PS_TERM_TBL
    									WHERE strm = '1128'--%Bind(NTSR_GDFT_AET.STRM)
    									AND institution = 'NT752'
    									AND acad_career = 'UGRD'
    									)
    									GROUP BY b.emplid
       				)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,383
    Rep Power
    391
    There are multiple problems with your query.

    Aggregate functions can not be used in a where clause, you should use a having clause.

    The between clause is misformed. The subquery is seen as one expression only.

    The expression in the select list of the subquery

    Code:
    term_begin_date AND term_end_date
    is interpreted as a boolean expression, not as two different values.

    One possible reformulation of your query is

    Code:
      and h.emplid in 
         (select PS_ATHL_PART_STAT.emplid 
            from PS_ATHL_PART_STAT
           cross
            join PS_TERM_TBL
           where PS_TERM_TBL.strm = '1128'
             and PS_TERM_TBL.institution = 'NT752'
             and PS_TERM_TBL.acad_career = 'UGRD')
           group
              by emplid
          having max(effdt) between PS_TERM_TBL.term_begin_date and PS_TERM_TBL.term_end_date)
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7
    Thanks. I ended up with something that looks like the following.


    sql Code:
    AND h.emplid IN(SELECT b.emplid
                         FROM PS_ATHL_PART_STAT b
                        WHERE b.effdt = (SELECT MAX(b2.effdt)
                                           FROM PS_ATHL_PART_STAT b2
                                          WHERE b2.emplid = b.emplid)
                          AND b.effdt >= (SELECT MIN(term_begin_dt)
                                            FROM PS_TERM_TBL
                                           WHERE acad_year = (SELECT acad_year
                                                                FROM PS_TERM_TBL
                                                               WHERE strm = '1128'--%Bind(NTSR_GDFT_AET.STRM)
                                                                 AND institution = 'NT752'
                                                                 AND acad_career = 'UGRD'))
                          AND b.effdt <= (SELECT MAX(term_end_dt)
                                            FROM PS_TERM_TBL
                                           WHERE acad_year = (SELECT acad_year
                                                                FROM PS_TERM_TBL
                                                               WHERE strm = '1128'--%Bind(NTSR_GDFT_AET.STRM)
                                                                 AND institution = 'NT752'
                                                                 AND acad_career = 'UGRD'))


    However I think yours looks more elegant. I wonder which one is more efficient?

IMN logo majestic logo threadwatch logo seochat tools logo