query w/ case help

hueby · #1 (**permalink**) 10-12-04, 16:58

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

Pat Phelan · #2 (**permalink**) 10-12-04, 18:43

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

hueby · #3 (**permalink**) 10-13-04, 10:14

Yes, that works! THANK YOU.

Internet Services

Web Development

Digital Marketing

Consumer Tech