Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ajreynolds
Starting Member
9 Posts |
 Posted - 2010-01-07 : 12:56:46
|
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_NumberWorkstationDate_EnteredThis 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_enteredselect 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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 13:02:52
|
| [code]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[/code] |
 |
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-07 : 13:32:18
|
| Tara, I don't think he wants an or statement in this case, if the part exists between workstations 5 through 10 then he wants the first date from those 6 workstations, if it does not exists then and only then give back a date from workstations 1-4. The OR statement would give the Min date of 1-4 if it exists there.Note the truth tables of ((p) OR ( P AND Q )) are equivelent to (p). Basically if p exists your entire statement is true, if p does not exist then the statement is false. |
|
|
|
ajreynolds
Starting Member
9 Posts |
Posted - 2010-01-07 : 14:10:52
|
| Bingo! Thank you very much Visakh, that is exactly what I was looking for. Thank you all for your responses. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-07 : 14:12:23
|
welcome  |
|
|
|
|
|
|
|
|
Combining statements with different ranges