Weights Table - 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

General SQL Server Forums

New to SQL Server Programming

Weights Table

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2010-03-30 : 10:06:02

Hello,
Sorry accidentaly put this in Sql Server 2008, this is the right place.

Can someone help me please.
I have a site and I have been asked by the client if I can add a postage cost based on the weight of the item(s
I need to create a table that will help do the calculation. I need to be able to select the correct record based on the weight calculation from the items chosen.).(I can already get the weight total)
So if the user picks 3 items with a combined weight of say 7.45kg, in the new table I need to have something like:
0 - 0.99kg £ 2.40
1 - 2.99kg £ 3.80
3 - 5.99kg £ 6.20
6 - 8.99kg £ 8.80

as the combined weight of my items is 7.45kg the select command will return £ 8.80. If the combined weight was 4.20kg it would return 6.20

How would I design the table I need and how would I write the select statement to select the right record based on the total weight.

Thanks for any help you can give I really appreciate it.

Best Regards,

Steve

Steve

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-30 : 10:19:46

[code]select postage
from wieghts
where @kg between lowRange and hiRange[/code]

Steve2106
Posting Yak Master

183 Posts

Posted - 2010-03-30 : 10:44:59

Hi Russell,
Thanks for the reply.

That looks easy enough.

I appreciate the help.

Best Regards,

Steve

Steve

Steve2106
Posting Yak Master

183 Posts

Posted - 2010-03-30 : 11:15:26

Hi Russell,

Added, Tested and works perfect.

Thankyou,

Steve.

Steve

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-30 : 13:33:09

Welocme

byrdzeye
Starting Member

14 Posts

Posted - 2010-04-02 : 12:01:38

np with russell solution but data is much easier to maintain if only the min or max is stored.

select top 1 postage from weights where weight <= @kg order by weight desc

Steve2106
Posting Yak Master

183 Posts

Posted - 2010-04-27 : 16:43:39

Hi There,

I'm back again.
I am trying to use the code that Russell gave but I am getting a dbnull returned from my stored procedure. This is my code in my .aspx page:

Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("DSN").ConnectionString)
myConnection.Open()

Dim myCommand As New SqlCommand("GetWeightPrice", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

'Create a SqlParameter object to hold the output parameter value
Dim WeightPriceParam As New SqlParameter("@WeightPrice", SqlDbType.Decimal)

'IMPORTANT - must set Direction as Output
WeightPriceParam.Direction = ParameterDirection.Output

'Finally, add the parameter to the Command's Parameters collection
myCommand.Parameters.Add(WeightPriceParam)

Dim WeightInParam As New SqlParameter("@WeightIn", SqlDbType.Decimal)
WeightInParam.Direction = ParameterDirection.Input
myCommand.Parameters.Add(WeightInParam)

'Call the sproc...
Dim reader As SqlDataReader = myCommand.ExecuteReader("2.20")

'Now you can grab the output parameter's value...
Dim WeightPrice As Decimal = Convert.ToDecimal(WeightPriceParam.Value)

This is the Stored Procedure:
CREATE PROCEDURE GetWeightPrice
(
@WeightIn decimal,
@WeightPrice decimal OUTPUT
)
AS

SET @WeightPrice = (
select postage
from tbWeights
where @WeightIn between LowRange and HighRange
)
GO

I would appreciate any help you can give.

Best Regards,

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources