Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45

    Unanswered: query w/ case help

    Hey all, here is my problem: I want to run a query to grab all the 'next dates' from a column after the user inputs a date. .. and then repeat accordingly.

    Here is some simplified example code: This works fine (but doesn't grab all rows, which varies, with the Date. It only grabs the first, hence the 'top 1')

    Code:
    select top 1 datewrk
    from hours
    where datewrk is not null and datewrk > '06/15/2004' and purchord = '4112'
    order by datewrk
    Soo, then this grabs all the rows with the Date, but doesn't 'skip' correctly. If you pick the date right before a valid row, as in there are rows of data for Date 6/18/2004 and you pick 6/17/2004 it will bring up the next date fine. BUT if you pick 6/15/2004 it will not 'skip ahead'. Any ideas??? Thanks

    Code:
    select datewrk
    from hours
    where datewrk is not null and datewrk > '06/15/2004' and 1 = (case when Datewrk = (select min(Datewrk) from Hours where Datewrk is not null and Datewrk > '06/15/2004') then 1 else 0 end) and purchord = '4112'
    order by datewrk

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does this work:
    Code:
    SELECT datewrk
       FROM hours
       WHERE  datewrk IS NOT NULL
          AND datewrk > '06/15/2004'
          AND purchord = '4112'
          AND 1 = (case when Datewrk = (select min(Datewrk)
             FROM Hours
             WHERE  Datewrk IS NOT NULL
                AND purchord = '4112'
                AND Datewrk > '06/15/2004') THEN 1 ELSE 0 END)
       ORDER BY datewrk
    -PatP

  3. #3
    Join Date
    Aug 2004
    Location
    STL
    Posts
    45

    Thumbs up

    Yes, that works! THANK YOU.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •