Check level with t-sql

Posted on 2011-10-31
Medium Priority
Last Modified: 2012-05-12

I have this table looking like this.
premiumID int
premiumName nvarchar
premiumLevel int

the data in the table can look like this
premiumID    premiumName    premiumLevel
1                   Goldcustomer   10000
2                   Silvercustomer   7500
3                   Bronscustomer   5000

Now I want to check what level a customers total shopping amount is. So for example if the customers amount is 6543 the query would return premiumID=3 since the amount is larger than 5000 but less then 7500. If the amount is 8500 the premiumID = 2 and so on. How can I achieve this with a t-sql query?

Thanks for help!

Question by:peternordberg
LVL 33

Accepted Solution

knightEknight earned 1600 total points
ID: 37059598

select top 1 premiumID
from customerPremium
where premiumLevel <= 6543   -- this is the amount spent
order by premiumID desc
LVL 12

Expert Comment

ID: 37059616
Select * From
  T.*,  ROW_NUMBER() OVER (Order by premiumLevel) as RN
  WHERE premiumLevel >=    myCustomerSpend
) Q
where RN = 1

This should return the first qualifying level

Assisted Solution

VipulKadia earned 400 total points
ID: 37061589
@ghtEknight : Minor mistake in your answer. In order by, I have replace 'premiumID' by 'premiumLevel'

select top 1 premiumID
from customerPremium
where premiumLevel <= 6543   -- this is the amount spent
order by premiumLevel desc
LVL 33

Expert Comment

ID: 37062597
yes, that was precisely my intent - good catch.

Author Closing Comment

ID: 37064585
Thanks for help!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question