Looping through a dataset of records.

Question

question

Batteryacid asked Jan 28, '10

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.

sql-server-2005 t-sql stored-procedures 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-28T19:10:37Z

TG answered Jan 28, '10

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

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