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

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    360
    Rep Power
    12

    Future dated effdt rows


    Another query I thought I had working, but a bug came up with a future dated effective dated row. I'm trying to capture those students that have a current sports participation row for the current term in the PS_ATHL_PART_STAT table. The problem comes up when they have a future dated row for fall and also one for summer. When they have two rows my query brings back nothing, but I need to bring back if they have a effdt row between the start and end dates of the current term.

    This is what is in the PS_ATHL_PART_STAT table

    7-15-2015 8-48-44 AM.jpg


    Here is my code.

    Code:
    SELECT b.*
      FROM PS_ATHL_PART_STAT b
      , PS_TERM_TBL d
     WHERE b.emplid = '11222333'
      AND b.CUR_PARTICIPANT = 'Y'
       AND b.effdt = (
     SELECT MAX(bb.effdt)
      FROM PS_ATHL_PART_STAT bb
     WHERE b.emplid = bb.emplid)
       AND d.strm = '1153'
       AND d.institution = 'NT752'
       AND d.acad_career = 'UGRD'
       AND b.effdt BETWEEN d.term_begin_dt AND d.term_end_dt;
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    867
    Rep Power
    392

    Cool


    I suspect this prevents future dates:
    Code:
    . . .
    AND b.effdt BETWEEN d.term_begin_dt AND d.term_end_dt;
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    360
    Rep Power
    12
    Originally Posted by LKBrwn_DBA
    I suspect this prevents future dates:
    Code:
    . . .
    AND b.effdt BETWEEN d.term_begin_dt AND d.term_end_dt;
    I don't want the future dated row. I want the row for the current Summer 2015 term. The one with the effdt of 05/18/2015

    And since the Summer term (1153) data looks like this

    2015 Summer
    term_begin_dt -05/18/2015 00:00:00
    term_end_dt -08/14/2015 00:00:00

    I don't know why I'm not returning the following if the effdt falls between the term begin and term end.

    11222333 AMF 05/18/2015 00:00:00 SCHOL N Y Fall 2015
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    867
    Rep Power
    392

    Cool


    1. Post table definitions.
    2. Post test data to reproduce problem test case.
    3. Provide your expected result set and explain the rules/reasons that lead to it.


IMN logo majestic logo threadwatch logo seochat tools logo