2

I need to create a table like this..

claim amount      court fee
exceed 300          15
301-500             30
501-1000            55

and so on..

I tried it my claim amount was nvarchar and court fee was float i get an error converting nvarchar to numeric.

When I try this query on select * from table where claim amount <=250 it displays all the rows. I am confused with what datatype should be used for claim amount and how can I be specific when retreiving the query like <=275...

Improve this question
3
  • 1
    I agree with @astader, a start and end value would be best. If you are adamant you want your database like so, then you would have to select everything and then use php and loop through the data exploding the field by the "-" and then testing the value with an if statement or something. Commented Oct 4, 2013 at 10:07
  • 1
    table structure is wrong , split column as posted below in answer . Also shouldn't exceed 300 be less than 300 ? Commented Oct 4, 2013 at 10:09
  • Which DBMS yre you using? Postgres offers range types which are exactly meant for that (and are very efficient as well) Commented Oct 4, 2013 at 12:27

2 Answers 2

Reset to default
3

I would create a table with a start and end value, with the start value null for opening entry and end value null for the closing value.

Something like

DECLARE @Table TABLE(
    ID INT IDENTITY (1,1),
    StartValue FLOAT,
    EndValue FLOAT,
    FeeValue FLOAT
)

INSERT INTO @Table SELECT null,300,15
INSERT INTO @Table SELECT 301,500,30
INSERT INTO @Table SELECT 501,1000,50
INSERT INTO @Table SELECT 1001,null,100

DECLARE @LookupValue FLOAT = 275

SELECT  *
FROm    @Table
WHERE   ISNULL(StartValue, @LookupValue) <= @LookupValue
AND     ISNULL(EndValue, @LookupValue) >= @LookupValue

SQL Fiddle DEMO

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I ma very much new to this concept astander, I am working on your query. Thanks
I tried it on sql fiddle it works perfectly but when i run the query on sql server it gives me error.......................... Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Line 24 Must declare the variable '@LookupValue'.
SELECT * FROm @courtfee where feevalue = 75 give me error Must declare the variable '@courtfee' please help.....
SELECT * FROm [Court Fees] where StartValue > 70 and EndValue < 300 returns empty rows
1

You should split this up into 2 columns making them both type int.

MinClaimAmount and MaxClaimAmount (otherwise you'll have to do string splitting and other nonsense).

MinClaimAmount  MaxClaimAmount     courtFee
0                  300                15
301                500                30
501                1000               55

Then, your SQL would be (if you wanted a specific row)

select * from  table where MinClaimAmount <= 275 AND MaxClaimAmount >= 275

SQL Fiddle

Improve this answer

6 Comments

the second query returns all rows
I am sorry to say that I tried your query but its not working.
I tried to delete the table and have created and now its works perfectly fine Ignore previous comment...
I am trying to inner join with other table and I have Payments in the new table can you please check this query select * from [ICPS].[dbo].[Courtfees] inner join [ICPS].[dbo].[vPCN_Payments] as a on a.Payments <= [ICPS].[dbo].[Courtfees]. [Min ClaimAmount] and a.Payments >= [ICPS].[dbo].[Courtfees].[Max Claim Amount]
I am sorry I thought to mark both answers as correct as i dont know that marking one answer unticks the other answer. I am really sorry
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.