Combining statements with different ranges

Question

question

Andy Reynolds asked Jan 7, '10

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 one?

I hope I've made sense.

Thanks very much, Andy

t-sql query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-01-07T15:51:39Z

Andy Reynolds answered Jan 7, '10

I guess combining them may not be the right thing to say then. The point is that I want to look at the records from workstations 5-10 first followed by workstations 1-4.

I've received a response on a different forum with the answer I was looking for. 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

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke commented · Feb 21, 2010 at 03:19 PM

Sorry I don't understand how that query has improved the speed over your original query other than the fact you are running one query and not two. In certain conditions your query could even run slower.

0 ·

Answer 2 · 2010-01-07T15:18:03Z

dave ballantyne answered Jan 7, '10

It would be counter productive to combine the two statements into one as, if it were in one statement, sqlServer would execute both parts anyway.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-02-21T14:36:59Z

Thomas 1 answered Feb 21, '10

First, posting some relevant schema and some sample data would make this significantly easier to solve. Second, is there any relationship of workstation to date_entered? I.e., if I enter part 'ABC' with workstation 1 on '2010-02-21', could I have a record for part 'ABC' with workstation 2 on '2000-01-01' (i.e. an earlier date)? Can a part be associated with the same workstation multiple times on multiple dates?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-03-02T12:53:57Z

Eric Templet answered Mar 2, '10

It would also increase your speed to have an index on the part numbers and include the workstation and date entered on the index. You would also want to make some statistics over time to increase the speed as well.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question