Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Unanswered: Combining statements with different ranges

    I'm trying to figure out a way to speed up a process. This process is trying to find the oldest record for a part number at various workstations in our system. The relevant fields in our table look like this:

    Part_Number
    Workstation
    Date_Entered

    This process needs to look first to see if the part exists at a specific workstation or workstations greater than the specified one, in workstation sequence. If still not found, it looks at all workstations, again in workstation sequence.

    Say, for example, I was looking for part number ABC. I have workstation numbers 1-10 and the specific workstation I want to start with is workstation 5. Workstation 3 is the only one with the part number at it.

    I would do the following lookups:

    select workstation, date_entered from inventory where part_number='ABC' and workstation>=5 order by workstation, date_entered;
    select workstation, date_entered from inventory where part_number='ABC' order by workstation, date_entered;

    If I came across the record for the part in the first lookup, I don't do the second look up.

    Is there a way to combine these statements into a single query? I was hoping to figure out how to put it into a single select statement and tell SQL to return workstations 5-10 first followed by 1-4 (in my example). Can something be done with the order by to do this instead of returning straight workstation sequence?

    I hope I've made sense.

    Thanks very much,
    Andy
    Last edited by ajreynolds; 01-07-10 at 15:02. Reason: clarification

  2. #2
    Join Date
    Nov 2009
    Posts
    5
    I received a response with the answer on a different forum. In case anyone is interested, the following does it:

    select top 1 workstation, date_entered from inventory where part_number='ABC' order by case when workstation>=@yourworkstation then 1 else 0 end desc,workstation, date_entered

    Andy

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Thanks for posting a followup!

Posting Permissions

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