Selecting a range with two fields

Question

I have a table with a job # and a suffix. The query i'm trying to create selects a range of job # / suffix entries where the suffix is dependant upon the job #. So for example:

Job #              Suffix
--------           -----------
00000001           001
00000001           002
00000001           003
00000002           001
00000002           002
00000002           003
00000002           004
00000003           001
00000003           002
00000003           003
00000003           004

I have four inputs. One pair is the starting job / suffix and then the ending job / suffix. If the user inputs:

Starting: 00000001 / 002

Ending: 00000002 / 002

They'll get the following results:

Job #              Suffix
--------           -----------
00000001           002
00000001           003
00000002           001
00000002           002

I want to know if there's a simple way to do this in one select statement in my stored proc. The only way I thought to do it so far would be to select the jobs first into a table variable then filtering the suffix with another query.

This seems simple, but having difficulty wrapping my brain around this one.

Thanks!

Answer 1

I'm not totally sure what you're trying to do, but a quick guess is something like this..

SELECT * FROM tbl
WHERE ( job + suffix*0.001 )
BETWEEN (starting_job + suffix*0.001) AND (ending_job + suffix*0.001)

This works, because it seems as if suffix is minor number. Essentially, you're serializing to "job.suffix" and selecting where you between the range of the set. If you can, create an index on (start_job + suffix*0.001). This also assumes suffix is of numeric(3)

Answer 2

You should be able to just combine the where clauses from your 2 queries into 1.

Something like

SELECT ...
WHERE 
     JOB# BETWEEN '00000001' and '00000002'
AND  Suffix BETWEEN '001' and '002'

EDIT: After your update I would do

SELECT ...
WHERE 
     JOB# + Suffix BETWEEN '00000001002'+ and '00000002002'

I assume values are strings with the leading 0's

Answer 3

I think you need to do something with a calculation in the where clause.

SELECT Table1.JOB, Table1.SUF
FROM Table1
WHERE (((Table1.JOB)>="00001" And (Table1.JOB)<="00002") AND (([JOB] & [SUF])>="00001002" And ([JOB] & [SUF])<="00002002"));

Answer 4

Here you go:

DECLARE @t TABLE (jobnum varchar(10), suffix varchar(3))

INSERT INTO @t (jobnum, suffix) VALUES ('00000001', '001')
INSERT INTO @t (jobnum, suffix) VALUES ('00000001', '002')
INSERT INTO @t (jobnum, suffix) VALUES ('00000001', '003')
INSERT INTO @t (jobnum, suffix) VALUES ('00000002', '001')
INSERT INTO @t (jobnum, suffix) VALUES ('00000002', '002')
INSERT INTO @t (jobnum, suffix) VALUES ('00000002', '003')
INSERT INTO @t (jobnum, suffix) VALUES ('00000002', '004')
INSERT INTO @t (jobnum, suffix) VALUES ('00000003', '001')
INSERT INTO @t (jobnum, suffix) VALUES ('00000003', '002')
INSERT INTO @t (jobnum, suffix) VALUES ('00000003', '003')
INSERT INTO @t (jobnum, suffix) VALUES ('00000003', '004')

DECLARE @Startjob VARCHAR(10)
DECLARE @Startsuf VARCHAR(3)
DECLARE @Endjob VARCHAR(10)
DECLARE @Endsuf VARCHAR(3)
SET @Startjob='00000001'
SET @Startsuf='002'
SET @Endjob='00000002'
SET @Endsuf='002'

;WITH raw AS 
(
SELECT jobnum, suffix, ind=RIGHT('0000000000'+ISNULL(jobnum,''),10)+RIGHT('000'+ISNULL(suffix,''),3)
    FROM @t
)
SELECT *
    FROM raw
    WHERE ind BETWEEN 
        RIGHT('0000000000'+ISNULL(@Startjob,''),10)+RIGHT('000'+ISNULL(@Startsuf,''),3)
        AND 
        RIGHT('0000000000'+ISNULL(@Endjob,''),10)+RIGHT('000'+ISNULL(@Endsuf,''),3)

There's a bunch of extraneous string manipulation in there in order to handle short-length and null values.