Looping through a record set repeatedly (in order to test between two values)

longinthetooth
longinthetooth used Ask the Experts™
on
I need to query the values in one table repeatedly against records in a temporary table, while the system_user is the same value ....
The temporary table @TmpTbl  has three columns and data as per the example
usr                     startvalue               endvalue
DOMAIN\user1     D10000                  D10500
DOMAIN\user1     D10600                  D10650
DOMAIN\user1     D10800                  D10859
DOMAIN\user1     D10950                  D10960

How can I find values in table DIM_DEPT where the values are between startvalue and endvalue for each row of the
@TmpTbl  where the usr = DOMAIN\user1
Simple "between" wouldnt work because the query returns more than one value each time?



Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SharathData Engineer

Commented:
Not sure what exactly you are looking for. This may be helpful for you. Change the value of rn as 1 or 2 or 3....etc to get the particular record from @TmpTbl
declare @TmpTbl as table(usr varchar(20),startvalue varchar(20),endvalue varchar(20))
insert into @TmpTbl values('DOMAIN\user1',     'D10000',                  'D10500')
insert into @TmpTbl values('DOMAIN\user1',     'D10600',                  'D10650')
insert into @TmpTbl values('DOMAIN\user1',     'D10800',                  'D10859')
insert into @TmpTbl values('DOMAIN\user1',     'D10950',                  'D10960')
select *
  from DIM_DEPT t1
  join (select *,row_number() over(partition by usr order by startvalue) as rn from @TmpTbl) as t2
    on t1.usr = t2.usr 
 where t1.usr = 'DOMAIN\user1' and t2.rn = 1

Open in new window

Here you go...first part is just to create sample tables to run as meas of a test,
--CREATE MY SAMPLE TEMP TABLE
CREATE TABLE #tmpTbl 
	(
	usr varchar(50),
	startvalue varchar(50),
	endvalue varchar(50)
	)
GO

--CREATE MY IMAGINARY DIM_DEPT TABLE
CREATE TABLE #DIM_DEPT
	(
	usr varchar(50),
	usrvalue varchar(50)
	)

--POPULATE MY SAMPLE TEMP TABLE
INSERT INTO #tmpTbl (usr,startvalue,endvalue) VALUES('DOMAIN\user1','D10000','D10500')
INSERT INTO #tmpTbl (usr,startvalue,endvalue) VALUES('DOMAIN\user1','D10600','D10650')
INSERT INTO #tmpTbl (usr,startvalue,endvalue) VALUES('DOMAIN\user1','D10800','D10859')
INSERT INTO #tmpTbl (usr,startvalue,endvalue) VALUES('DOMAIN\user1','D10950','D10960')
GO

--POPULATE MY IMAGINARY DIM_DEPT TABLE WITH RANDOM DATA
INSERT INTO #DIM_DEPT (usr,usrvalue) VALUES('DOMAIN\user1','D10001')
INSERT INTO #DIM_DEPT (usr,usrvalue) VALUES('DOMAIN\user1','D10450')
INSERT INTO #DIM_DEPT (usr,usrvalue) VALUES('DOMAIN\user1','D10602')
INSERT INTO #DIM_DEPT (usr,usrvalue) VALUES('DOMAIN\user1','D11000')
INSERT INTO #DIM_DEPT (usr,usrvalue) VALUES('DOMAIN\user1','D10955')
INSERT INTO #DIM_DEPT (usr,usrvalue) VALUES('DOMAIN\user1','D10803')
INSERT INTO #DIM_DEPT (usr,usrvalue) VALUES('DOMAIN\user1','D55555')
GO

--REMOVE LEADING ALPHA-CHARACTER AND CREATE STORE MY RANGE ON VARIABLES
DECLARE @startvalue AS INT
DECLARE @endvalue AS INT


SELECT * FROM #DIM_DEPT AS dp
INNER JOIN #tmpTbl AS t
	ON dp.usr = t.usr
	AND REPLACE(dp.usrvalue,'D','') BETWEEN REPLACE(t.startvalue,'D','') AND REPLACE(t.endvalue,'D','')

Open in new window

And here is the result dataset
Untitled-picture.png

Author

Commented:
Thanks very much! It is actually so logical, but I didnt and couldnt figure it out

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial