Select from Table Containing Ranges

Question

Select from Table Containing Ranges

steve.walls

SSC Enthusiast

Points: 133
More actions
June 8, 2010 at 3:23 pm

#157008

Hi I wonder if anyone can help. I have the following code to update table one from table two and it does not work...
CREATE TABLE #Bookings (
BookingNumber int,
People Int,
Distance int,
Cost money)
INSERT INTO #Bookings (BookingNumber, People, Distance, Cost)
VALUES (1,1,3,0); -- Cost Should be calculated as 1 person going under 5 miles = £10
INSERT INTO #Bookings (BookingNumber, People, Distance, Cost)
VALUES (2,3,7,0); -- Cost should be calculated as under 5 people going over 5 miles but under 10 Miles = £50.00
CREATE TABLE #travelRates (
UpToDistance int,
UpToPeople Int,
Charge money)
INSERT INTO #travelRates (UpToDistance, UpToPeople, Charge)
VALUES(5,1,10.00);
INSERT INTO #travelRates (UpToDistance, UpToPeople, Charge)
VALUES(5,5,30.00);
INSERT INTO #travelRates (UpToDistance, UpToPeople, Charge)
VALUES(10,1,18.00);
INSERT INTO #travelRates (UpToDistance, UpToPeople, Charge)
VALUES(10,5,50.00);
INSERT INTO #travelRates (UpToDistance, UpToPeople, Charge)
VALUES(15,1,27.00);
INSERT INTO #travelRates (UpToDistance, UpToPeople, Charge)
VALUES(15,5,70.00);
WITH Rates_CTE (UpToDistance, UpToPeople, Charge)
AS
-- Define the CTE query.
(
SELECT UpToDistance, UpToPeople, Charge
FROM #travelRates
)
-- Here is where I am going wrong
UPDATE #Bookings
SET Cost = #travelRates.Charge
From #travelRates,
Rates_CTE
WHERE
#Bookings.Distance <= (SELECT MIN(UpToDistance) as UpToDistance FROM Rates_CTE WHERE UpToDistance >= #Bookings.Distance) AND
#Bookings.People <= (SELECT Min(UpToPeople) as UpToPeople FROM Rates_CTE WHERE UpToPeople >= #Bookings.People)
-- Above is where I am goign wrong somehow
SELECT * from #Bookings; -- Display result. Booking 1 should be £10 and is (by default of being line 1 I think and Booking 2 should be £50 while its also comes wrongly as line 1 £10.
SELECT * FROM #travelRates; -- Display rates for visible view of rates shored.
DROP TABLE #Bookings;
DROP TABLE #travelRates;
In this example if 3 people were to book a journey for 7 miles it would cost £50. Its above 5 miles and below 10. it is more than 1 person and less than 5 so it would be the following result line I require 10,5, £50
I want to update Table1 with the correct Cost selected from the Charge field of Table2.
I am trying to update the table in a single update statement using a Common Table Expression and drawing a blank with the update query.
Anyone have any ideas to help, also anyone think of a different method? Any ideas welcome.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Steve,

I realize that you're pretty new to this web site. Please realize that we are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

steve.walls SSC Enthusiast Points: 133 More actions · Answer 2

Jeff, first off apologies for bland initial post as I was working on the basis someone would respond with the format of an UPDATE statement from their head without considering the need to test due to complication.

I have now created a test table and would ask the following

I have two tables.

1) Passenger bookings made and their charge.

2) Tariff for passengers/journeys.

The complication for me is that the tariff is all in bands. For instance for 1 person its one rate, but then next step is 2-5 people its another rate.

Added to that the complication again of distance. So the passengers and Distance are both UP TO bands and cannot therefore be used as EQUALS in a select statement for updating my passenger bookings table with the charge.

I have tried to use a CTE but think I have confused myself with the combination of CTE and table resulting in me always getting the first result of the table as my answer irrespective of the values.

Can anyone help with the following code?

CREATE TABLE #Bookings (

BookingNumber int,

People Int,

Distance int,

Cost money)

INSERT INTO #Bookings (BookingNumber, People, Distance, Cost)

VALUES (1,1,3,0); -- Cost Should be calculated as 1 person going under 5 miles = £10

INSERT INTO #Bookings (BookingNumber, People, Distance, Cost)

VALUES (2,3,7,0); -- Cost should be calculated as under 5 people going over 5 miles but under 10 Miles = £50.00

CREATE TABLE #travelRates (

UpToDistance int,

UpToPeople Int,

Charge money)