June 27, 2014 at 11:48 am
I have the following recordset:
cmdBatchNbPdsLbsZONE
817159644 1.55320031
817159652 9.09590031
817159679 2.5891806
817159687 5.7123006
817159709 2.3903006
817159733 2.2792006
817159741 2.0647007
817159768 1.2430007
817159784 4.1547006
817159792 3.56576013
I need to extract the corresponding price from the following table:
Zone MaxWeight Price
---------------------- ---------------------------------------
31 1.70 7.14
31 2.20 8.76
31 3.30 9.47
31 4.40 9.69
31 5.50 10.61
31 6.60 11.05
31 7.70 11.49
31 8.80 11.93
31 9.90 12.37
31 11.00 12.81
31 12.10 13.23
In this case, the 2 first rows should give a price of
1) 7.14 (weight between 0 - 1.70)
2) 11.93 (weight between 8.80 - 9.90)
How can I do that with a query?? I haven't figured it out yet.
thanks a lot for your time and help
June 27, 2014 at 12:14 pm
Dominic Gagné (6/27/2014)
I have the following recordset:cmdBatchNbPdsLbsZONE
817159644 1.55320031
817159652 9.09590031
817159679 2.5891806
817159687 5.7123006
817159709 2.3903006
817159733 2.2792006
817159741 2.0647007
817159768 1.2430007
817159784 4.1547006
817159792 3.56576013
I need to extract the corresponding price from the following table:
Zone MaxWeight Price
---------------------- ---------------------------------------
31 1.70 7.14
31 2.20 8.76
31 3.30 9.47
31 4.40 9.69
31 5.50 10.61
31 6.60 11.05
31 7.70 11.49
31 8.80 11.93
31 9.90 12.37
31 11.00 12.81
31 12.10 13.23
In this case, the 2 first rows should give a price of
1) 7.14 (weight between 0 - 1.70)
2) 11.93 (weight between 8.80 - 9.90)
How can I do that with a query?? I haven't figured it out yet.
thanks a lot for your time and help
Nobody else can figure this out either because you haven't posted enough details. Why 7.14? Where does that come from?
Please post ddl (create table scripts) and sample data (insert statements) along with the desired output based on your sample data. See the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2014 at 12:38 pm
Sorry about that.
create table #PricePerZone
(
Zone1 int,
WeightLbs double,
Price double
)
insert into #PricePerZone values (31, 1.70, 7.14)
insert into #PricePerZone values (31, 2.20, 8.76)
insert into #PricePerZone values (31, 3.30, 9.47)
insert into #PricePerZone values (31, 4.40, 9.69)
insert into #PricePerZone values (31, 5.50, 10.61)
insert into #PricePerZone values (31, 6.60, 11.05)
insert into #PricePerZone values (31, 7.70, 11.49)
insert into #PricePerZone values (31, 8.80, 11.93)
insert into #PricePerZone values (31, 9.90, 12.37)
insert into #PricePerZone values (31, 11.00, 12.81)
create table #Temp (
cmdBatchNb varchar(25),
PdsLbs double,
Zone int )
insert into #Temp values ('817159644', 1.5532, 31)
insert into #Temp values('817159652', 9.0959, 31)
what I need to extract is the Price field in the #PricePerZone table, according to the Zone and PdsLbs fields of the #Temp table. Like state earlier, the first batch (817159644) should gives 7.14 and the second one (817159652) should gives 11.93.
thanks
June 27, 2014 at 12:57 pm
Here is the code I developed:
create table #Batches(
cmdBatchNb bigint,
PdsLbs decimal(10,6),
Zone tinyint);
insert into #Batches
values
(817159644,1.553200,31),
(817159652,9.095900,31),
(817159679,2.589180,6),
(817159687,5.712300,6),
(817159709,2.390300,6),
(817159733,2.279200,6),
(817159741,2.064700,7),
(817159768,1.243000,7),
(817159784,4.154700,6),
(817159792,3.565760,13);
create table #ZoneRates(
Zone tinyint,
MaxWeight decimal(10,6),
Price decimal(10,2));
insert into #ZoneRates
values
(31,1.70,7.14),
(31,2.20,8.76),
(31,3.30,9.47),
(31,4.40,9.69),
(31,5.50,10.61),
(31,6.60,11.05),
(31,7.70,11.49),
(31,8.80,11.93),
(31,9.90,12.37),
(31,11.00,12.81),
(31,12.10,13.23);
select
b.*,
ca.Price
from
#Batches b
cross apply (select top (1) zr.Price from #ZoneRates zr where b.PdsLbs <= zr.MaxWeight and b.Zone = zr.Zone order by zr.MaxWeight asc)ca(Price)
go
drop table #Batches;
drop table #ZoneRates;
go
I disagree with your evaluation for the Batch with a weight of 9.095900. Since the ZoneRates column is called MaxWeight, I would make the assumption that any package weighing MORE than that weight would be charged at the higher rate where MaxWeight is listed as 9.90 since the weight is between 8.80 and 9.90.
June 27, 2014 at 1:08 pm
You're totally right, my mistake!
thanks a lot for your kindness and fast answer! It works perfectly!
I'll learn a couple things from your solution. thanks! 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply