Need help

[adnancanada]

I have rate for every PO depends on its weight. We have column for weight bracket if it falls under weight bracket I need to pickup that rate.

in Below example this PO 4859554638 has weight 1070 lb it falls in weight bracket 999 it should pickup only $25.86
in 2nd PO 4859606472 has weight 546 lb it falls in weight bracket 499 then it should pickup $26.43

CUSTOMER	PO_NUM	PO_WGT	Wgt_Brackets	RatePerKg
3009848B28	4859554638	1070	499	$29.74
3009848B28	4859554638	1070	999	$25.86
3009848B28	4859554638	1070	1999	$22.31
3009848B28	4859554638	1070	4999	$21.30
3009848B28	4859554638	1070	9999	$12.15
3009848B28	4859554638	1070	19999	$8.86
3009848B28	4859554638	1070	29999	$6.88
3009848B28	4859554638	1070	39999	$5.40
3009848B28	4859554638	1070	999999	$4.59
3402427A28	4859606472	546	499	$26.43
3402427A28	4859606472	546	999	$17.24
3402427A28	4859606472	546	1999	$11.90
3402427A28	4859606472	546	4999	$10.14
3402427A28	4859606472	546	9999	$8.86
3402427A28	4859606472	546	19999	$8.44
3402427A28	4859606472	546	29999	$7.63
3402427A28	4859606472	546	39999	$7.52
3402427A28	4859606472	546	999999	$6.37

I need my result in 3 column summary


Customer PO_num RatePerkg
3009848B28 4859554638 $25.86
3402427A28 4859606472 $26.43

I need to use iif condition if PO_wgt>Weight bracket then rate

thanks in advance. your help is appreciated.

[Welshgasman]

Little confused here.

Where are you getting those figures from?

Also you have multiple same weights which all have different weight brackets? How is that?

[CJ_London]

You need to define the rules - typically you will have a weight range and a price

In your first paragraph you say

if it falls under weight bracket

and in the last you say

if PO_wgt>Weight

[NoellaG]

I think you should have 2 tables: one with the tarifs for PO's per weight class, and one with the actual weights.

[orange]

adnancanada,

Please provide some detail regarding weight or weight bracket or weight range and Price.
Weight bracket would typically involve 2 values defining a range. LowValue----HighValue
Are weight rates the same for all customers or varied?
A few examples would help readers with context of/for your request.

[adnancanada]

Little confused here.

Where are you getting those figures from?

Also you have multiple same weights which all have different weight brackets? How is that?

Sorry there are two tables customer id is common in both of them.

TABLE 1

CUST_ID	PO_NUM	PO_WGT
3009848B28	4859554638	1070
3402427A28	4859606472	546

TABLE 2

CUST_ID	Wgt_Brackets	RatePerlb
3009848B28	499	$29.74
3009848B28	999	$25.86
3009848B28	1999	$22.31
3009848B28	4999	$21.30
3009848B28	9999	$12.15
3009848B28	19999	$8.86
3009848B28	29999	$6.88
3009848B28	39999	$5.40
3009848B28	999999	$4.59
3402427A28	499	$26.43
3402427A28	999	$17.24
3402427A28	1999	$11.90
3402427A28	4999	$10.14
3402427A28	9999	$8.86
3402427A28	19999	$8.44
3402427A28	29999	$7.63
3402427A28	39999	$7.52
3402427A28	999999	$6.37

[adnancanada]

adnancanada,

Please provide some detail regarding weight or weight bracket or weight range and Price.
Weight bracket would typically involve 2 values defining a range. LowValue----HighValue
Are weight rates the same for all customers or varied?
A few examples would help readers with context of/for your request.

wgt_bracket
0-499 lbs
500-999 lbs
1000-1999 lbs
2000-4999 lbs
5000-9999 lbs
10000-19000 lbs
20000-29000 lbs
30000-39999 lbs
>40000

if the PO_wgt fall under this category then pickup rate accordingly.

[adnancanada]

You need to define the rules - typically you will have a weight range and a price

In your first paragraph you say


and in the last you say

it should be under wgt bucket

wgt_bracket
0-499 lbs
500-999 lbs
1000-1999 lbs
2000-4999 lbs
5000-9999 lbs
10000-19000 lbs
20000-29000 lbs
30000-39999 lbs
>40000

[orange]

?? Why do you describe wgt_bracket in pounds and have a RatePerKG?

??Are weight rates the same for all customers or varied?

Consider:

Customer A , PO # 24, PO_wgt 600 lbs
Customer B, PO #3333, PO_wgt 2600 lbs
Customer B, PO #3005, PO_wgt 600 lbs

Based on your numbers in #7 and #8, what would be the RatePerKG for these 3 examples?

[adnancanada]

?? Why do you describe wgt_bracket in pounds and have a RatePerKG?

??Are weight rates the same for all customers or varied?

Consider:

Customer A , PO # 24, PO_wgt 600 lbs
Customer B, PO #3333, PO_wgt 2600 lbs
Customer B, PO #3005, PO_wgt 600 lbs

Based on your numbers in #7 and #8, what would be the RatePerKG for these 3 examples?

Weight are same for all customer but rates are different. sorry it is RatePerlb

[CJ_London]

your example data has gaps

think these

10000-19000 lbs
20000-29000 lbs
30000-39999 lbs
>40000

should be
10000-19999 lbs
20000-29999 lbs
30000-39999 lbs
>=40000

and that assumes you do not have fractions of a lb

suggested query

SELECT myTable.*, RateTable.Price
FROM myTable, RateTable 
WHERE myTable.weight BETWEEN RateTable.Lower and RateTable.Upper

edit: rather than >=40000

store 40000 as lower and a really high number for Upper such as 999999