Clarification - 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)

Clarification

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-07 : 16:11:59

This is my sample table
declare @cband table (issue_basis int,band_amount int,band_number int)
insert @cband
select 251,99999,01 UNION ALL
select 251,249999,02 UNION ALL
select 251,999999,03 UNION ALL
select 251,999999999,04 UNION ALL
select 252,99999,01 UNION ALL
select 252,249999,02 UNION ALL
select 252,999999,03 UNION ALL
select 252,999999999,04

I need to select the band_number for a particular issue_basis and a face_amount which i will pass into the "select". I need to choose that particular band_number where the face_amount is within the limit od band_amount. For example..if the face_amount is 100000, and issue_basis 251, the band_number should be "02" (greater than 99999 but less than 249999). This is what I wrote using TOP. Looks simple..not sure if i missed something.

select top 1 band_number from @cband
where issue_basis = @issue_basis and band_amount > @face_amount
order by band_number

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-07 : 19:51:25

Here is some code, I wrote it so you can plug in the band# and it will return what it would be for every issue_bassis to illustrate how you can use it.


Declare @BandNo int
set @BandNo = 100000

declare @cband table (issue_basis int,band_amount int,band_number int)
insert @cband 
select 251,99999,01 UNION ALL
select 251,249999,02 UNION ALL
select 251,999999,03 UNION ALL
select 251,999999999,04 UNION ALL
select 252,99999,01 UNION ALL
select 252,249999,02 UNION ALL
select 252,999999,03 UNION ALL
select 252,999999999,04 

Select Issue_Basis, Band_Number
from
(Select row_Number() over (Partition by aa.issue_Basis order by aa.Band_Amount) as RowID,*
from
@cband aa
where aa.band_Amount > @BandNo
) a
where a.RowID = 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources