CODEWITHSUNDEEP
sql-server-2008loops
HL8
HL8

Reputation: 1419

SQL Server 2008 - excluding from number of ranges of numbers

I need help with a SQL script.

I have an orders table with a card_number column and I have another table called card_range which stores card_number ranges per client with a column to flag whether it's active or not active.

When I select data from the orders table I would like to exclude all card_number ranges which are inactive

Eg. orders table

order_id   card_number
1          101
2          102
3          201
4          301

card_range table, active 0 = no, 1 = yes

start_card_number end_card_number active
101               199             0
201               299             1
301               399             0

So the only data I want to return from the orders table is

order_id card_number
3        201

The script that I'm trying to figure out is the looping thru the card_range table...thanks

Upvotes: 0

Views: 125

Answers (1)

HABO
HABO

Reputation: 15841

Assuming that card ranges don't overlap:

declare @orders as table ( order_id int, card_number int )
insert into @orders ( order_id, card_number ) values
  ( 1, 101 ), ( 2, 102 ), ( 3, 201 ), ( 4, 301 )

declare @card_range as table ( start_card_number int, end_card_number int, active bit )
insert into @card_range ( start_card_number, end_card_number, active ) values
  ( 101, 199, 0 ), ( 201, 299, 1 ), ( 301, 399, 0 )

select order_id, card_number
  from @orders as o inner join
    @card_range as cr on
      cr.start_card_number <= o.card_number and o.card_number <= cr.end_card_number and
      cr.active = 1

Upvotes: 1

Related Questions