x

Looping through a dataset of records.

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?

how should the query look which can return values from the DIM_DEPT - in a table format, a single column, for the all values found between the start and end values of each row of the @TmpTbl?

This resulting table will be used as a parameter list in a SSRS report.

more ▼

asked Jan 28, 2010 at 12:52 AM in Default

avatar image

Batteryacid
13 4 4 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Just a simple JOIN should do it:

--set up your data for testing ;with TmpTbl (usr, startvalue, endvalue) as ( select 'DOMAIN\user1', 'D10000', 'D10500' union all select 'DOMAIN\user1', 'D10600', 'D10650' union all select 'DOMAIN\user1', 'D10800', 'D10859' union all select 'DOMAIN\user1', 'D10950', 'D10960' ) ,DIM_DEPT (dept) --some departments as ( select 'A00001' union all select 'D10001' union all select 'D10499' union all select 'E10499' union all select 'DA' union all select 'D10955' union all select 'D10500' )

--Your actual query
select d.dept
from Dim_Dept as d
inner join TmpTbl t
on t.startValue <= d.dept
and t.endValue >= d.dept

output:
dept


D10001
D10499
D10500
D10955

more ▼

answered Jan 28, 2010 at 07:10 PM

avatar image

TG
1.8k 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2031
x1090
x476
x451

asked: Jan 28, 2010 at 12:52 AM

Seen: 1382 times

Last Updated: Jan 28, 2010 at 12:52 AM

Copyright 2017 Redgate Software. Privacy Policy