Select rows not in a range. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select rows not in a range.

Author

Topic

sbosarge
Starting Member

3 Posts

Posted - 2009-05-20 : 10:58:46

First, thanks for reading.

Second, I have this issue working but not as efficiently as i would like so i am asking for a few more eyes.

my problem...
I have table "A" that has 20-30 rows with 2 columns. column 1 is a decimal and is the start of a numeric range. column 2 is simply the end of the range. a row looks like 7007.0000 - 7007.9999. so i have about 30 rows with various ranges. none of the ranges overlap from row to row and column 2 is always larger than column 1. this table is actually a list of account rnages

Table "B" is a list of accounting transactions, one row equals 1 atomic transaction. rows are not related to each other within the table, there are several columns but the important ones are the account number and the amount of the transaction and the id column which is an identity column. there can be upwards of 10,000 rows in this table.

My task is to only sum rows from table "B" that do not fall within any of the ranges from table "A"

here is what i came up with...

SELECT distinct B.id,B.year,B.period,B.location, cast(B.acct as decimal(10,4))as acctnumber
into #temp
FROM B JOIN A on 
cast(B.acct as decimal(10,4)) between A.acctRangeStart and  A.acctRangeEnd) OR (A.acctRangeStart is null)
WHERE     (B.YEAR = '2009') AND (B.period = '5') AND (B.location = '123456') 

GO

select sum(transamt) from B where B.id not in (select id from #temp) and (B.YEAR = '2009') AND (B.PER = '5') AND (B.location = '123456')

GO

The first query returns all of the rows from table B that ARE within the ranges in table A and inserts them into a temp table.

the second query looks at table B and selects everything that is not in the temp table and that meets the other conditions in the where clause...

I think my logic is working but i dont feel it is very efficient...

some other notes: this is a sql 2005 server and the data is comming from a reporting database not a transactional one.

Thanks a million... Scott

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-05-20 : 11:31:25

[code]SELECT SUM(transamt)
FROM TableB B
WHERE B.Year = '2009' -- is this really a string?
AND B.PER = '5' -- is this really a string?
AND B.location = '123456' -- is this really a string?
AND NOT EXISTS
(
SELECT *
FROM TableA A
-- proceesing an AccountNumber as a decimal seems very dodgy
-- the cast will result in a table scan
WHERE CAST(B.acct AS decimal(10, 4)) BETWEEN A.acctRangeStart AND A.acctRangeEnd
OR A.acctRangeStart IS NULL
)
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 13:01:38

[code]
SELECT SUM(transamt)
FROM B
LEFT JOIN A on
cast(B.acct as decimal(10,4)) between A.acctRangeStart and A.acctRangeEnd) OR (A.acctRangeStart is null)
WHERE (B.YEAR = '2009') AND (B.period = '5') AND (B.location = '123456')
AND A.acctRangeStart IS NULL

GO
[/code]

sbosarge
Starting Member

3 Posts

Posted - 2009-05-20 : 14:05:23

Ifor, thank you for the reply. to answer your Q; these are strings and the table scan is not really a problem due to the size of the db. and I agree with you that this is not an ideal setup... inheritance is a b**ch sometimes

visakh16, thank you for the reply as well. I had actually started out with the left join but i also added in some additional logic that was causing the query to return all rows so i moved to the solution i presented in my original post... I used your solution as a logical template and came up with the query below that returns the same results as my super complicated query. Thanks Again!

declare @year char(4),@period char(2) ,@location char(6)
set @year = '2009'
set @period = '5'
set @location = '123456'

select
COALESCE
((select sum(amount)
from B
--from PRJwtn as B
left join A on
((
case
when b.obj is null then cast(b.acct as decimal(10,4))
when (b.sub = '' or b.sub is null) then cast(b.obj as decimal(10,4))
else cast(b.obj + '.' + b.sub as decimal(10,4))
end
) between A.acctRangeStart and A.acctRangeEnd) OR (A.acctRangeStart is null)
WHERE (B.YEAR = @year) AND (B.period = @period) AND (B.location = @location)
AND A.acctRangeStart IS NULL)
,0.00)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources