• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Check level with t-sql

Hi,

I have this table looking like this.
customerPremium
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!

Peter
0
Peter Nordberg
Asked:
Peter Nordberg
2 Solutions
 
knightEknightCommented:

select top 1 premiumID
from customerPremium
where premiumLevel <= 6543   -- this is the amount spent
order by premiumID desc
0
 
Paul_Harris_FusionCommented:
Select * From
(
  Select
  T.*,  ROW_NUMBER() OVER (Order by premiumLevel) as RN
  WHERE premiumLevel >=    myCustomerSpend
) Q
where RN = 1

This should return the first qualifying level
0
 
VipulKadiaCommented:
@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
0
 
knightEknightCommented:
yes, that was precisely my intent - good catch.
0
 
Peter NordbergIT ManagerAuthor Commented:
Thanks for help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now