working with ranges - 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)

working with ranges

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-12-17 : 12:15:49

I am looking to calculate a delivery cost based on an incoming total order value and postcode.

The delivery cost should be determined by finding the range within which the amount falls, ie $0-500, $501-1000, $1001-2000, 2001-5000 etc


          $0-500, $501-1000, $1001-2000, 2001-5000  
Postcode      
6000        $80       $120     $150        $200
6015        $80       $140     $140        $211
6030        $80       $120     $140        $230
6040        $80       $130     $130        $230
6215        $80       $124     $150        $205

What would be the best approach to do this with the following input parameters...

@Postcode char(4)
@OrderValue money

I am guessing I should create a table with a postcode for each row and a separate column for low/high and then use Min/Max and between operators, but not sure if that is the best way.

Secondly, and probably more importantly, I am wondering how to reorganise all of that data (programatically) so that each postcode will have its own row with the high/low value columns, and still retain the amount in each row.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-17 : 12:34:04

[code]declare @Spreads table (Postcode char(4), [$0-500] money, [$501-1000] money, [$1001-2000] money, [2001-5000] money)
insert @Spreads
select 6000, $80, $120, $150, $200
union all select 6015, $80, $140, $140, $211
union all select 6030, $80, $120, $140, $230
union all select 6040, $80, $130, $130, $230
union all select 6215, $80, $124, $150, $205

declare @Postcode char(4), @OrderValue money
set @Postcode = '6040'
set @OrderValue = 123

; with GoodCandidateForAViewOrBetterYetStructureTheTableLikeThisRatherThanThePoorStructureYouHave
(Postcode, ValueFrom, ValueTo, OrderValue) as (
select Postcode, 0, 500, [$0-500] from @Spreads
union all select Postcode, 501, 1000, [$501-1000] from @Spreads
union all select Postcode, 1001, 2000, [$1001-2000] from @Spreads
union all select Postcode, 2001, 5000, [2001-5000] from @Spreads)
select * from GoodCandidateForAViewOrBetterYetStructureTheTableLikeThisRatherThanThePoorStructureYouHave
where Postcode = @Postcode and @OrderValue between ValueFrom and ValueTo[/code]

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-12-17 : 21:55:40

that's a spreadsheet layout, not a table, but thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources