Can someone help with query....

The following script creates the SQL version of my tables and records.

You will see that there is one purchase order for a total of 11 items which
are packed in two package types (flat and cube). What I need is to determine
the total cost for shipping, handling and number of parcels, based on the
following:

The basic cost of a package is fixed (eg: £10.00) for the first 8kg and an
extra cost £0.50/kg up to the maximum package weight.

So my inventory is

Package Type 1
4x product 1 @ 3.0kg = 12.0kg
2x product 2 @ 3.5kg = 7.0kg
5x product 5 @ 1.0kg = 5.0kg
== 2 Parcels totalling £24.00 as
1 @ 16kg (£10 base + 8 x 50p extra = £14.00)
1 @ 8kg (£10 base)

Package Type 2
1x product 2 @ 6.0kg = 6.0kg
== 1 Parcel totalling £10.00 as
1 @ 6kg (£10 base)

*** RESULTS THAT I NEED ***
Shipping = £34.00
Parcels = 3
Handling = ((Shipping / 100) x HandlingPercentageRate) + (Parcels x
HandlingPackageRate)


------------ START OF SCRIPT ------------

/* Create the PRODUCTS table */

create table products
(
product_id int identity primary key,
title varchar(255),
details varchar(2000),
price smallmoney,
package_type_id int,
weight real
)

/* Assign some products */

insert into products (title, details, price, package_type_id, weight)
values ('Title 1', '1000 x 400 x 40mm (Economy)', 10.00, 1, 3.0)

insert into products (title, details, price, package_type_id, weight)
values ('Title 2', '1000 x 400 x 50mm (Standard)', 12.50, 1, 3.5)

insert into products (title, details, price, package_type_id, weight)
values ('Title 3', '1000 x 400 x 60mm (Deluxe)', 15.00, 1, 4.0)

insert into products (title, details, price, package_type_id, weight)
values ('Title 4', '600 x 600 x 150mm', 25.00, 2, 6.0)

insert into products (title, details, price, package_type_id, weight)
values ('Title 5', '1000 x 20 x 20', 3.50, 1, 1.0)

/* Create the PACKAGE TYPES */

create table package_types
(
package_type_id int identity primary key,
max_weight real,
comments varchar(255)
)

/* Assign package types */

insert into package_types (max_weight, comments) values (16, 'Flat')
insert into package_types (max_weight, comments) values (12, 'Cube')

/* Create a PURCHASE ORDER table */

create table purchase_order
(
purchase_order_id int identity primary key,
email_address varchar(255),
item_count int default 0,
packages int default 0,
shipping smallmoney default 0
)

/* Add a purchase order */

insert into purchase_order(email_address)
values ('email@address.com')

/* Create an INVENTORY table */

create table purchase_order_items
(
purchase_order_item_id int identity primary key,
purchase_order_id int,
product_id int,
quantity int,
)

/* Assign some items to the purachse order */

insert into purchase_order_items (purchase_order_id, product_id, quantity)
values (1, 1, 4)
insert into purchase_order_items (purchase_order_id, product_id, quantity)
values (1, 2, 2)
insert into purchase_order_items (purchase_order_id, product_id, quantity)
values (1, 4, 1)
insert into purchase_order_items (purchase_order_id, product_id, quantity)
values (1, 5, 5)

/* View the results */

select * from products
select * from package_types
select * from purchase_order
select * from purchase_order_items

------------ END OF SCRIPT ------------

On 28 Mar, 12:33, "Sean" <sean.anderson@[nospam]oakleafgroup.biz>
wrote:
> The following script creates the SQL version of my tables and records.
>
> You will see that there is one purchase order for a total of 11 items which
> are packed in two package types (flat and cube). What I need is to determine
> the total cost for shipping, handling and number of parcels, based on the
> following:
>
> The basic cost of a package is fixed (eg: £10.00) for the first 8kg andan
> extra cost £0.50/kg up to the maximum package weight.
>
> So my inventory is
>
> Package Type 1
> 4x product 1 @ 3.0kg = 12.0kg
> 2x product 2 @ 3.5kg = 7.0kg
> 5x product 5 @ 1.0kg = 5.0kg
> == 2 Parcels totalling £24.00 as
> 1 @ 16kg (£10 base + 8 x 50p extra = £14.00)
> 1 @ 8kg (£10 base)
>
> Package Type 2
> 1x product 2 @ 6.0kg = 6.0kg
> == 1 Parcel totalling £10.00 as
> 1 @ 6kg (£10 base)
>
> *** RESULTS THAT I NEED ***
> Shipping = £34.00
> Parcels = 3
> Handling = ((Shipping / 100) x HandlingPercentageRate) + (Parcels x
> HandlingPackageRate)
>
> ------------ START OF SCRIPT ------------
>
> /* Create the PRODUCTS table */
>
> create table products
> (
> product_id int identity primary key,
> title varchar(255),
> details varchar(2000),
> price smallmoney,
> package_type_id int,
> weight real
> )
>
> /* Assign some products */
>
> insert into products (title, details, price, package_type_id, weight)
> values ('Title 1', '1000 x 400 x 40mm (Economy)', 10.00, 1, 3.0)
>
> insert into products (title, details, price, package_type_id, weight)
> values ('Title 2', '1000 x 400 x 50mm (Standard)', 12.50, 1, 3.5)
>
> insert into products (title, details, price, package_type_id, weight)
> values ('Title 3', '1000 x 400 x 60mm (Deluxe)', 15.00, 1, 4.0)
>
> insert into products (title, details, price, package_type_id, weight)
> values ('Title 4', '600 x 600 x 150mm', 25.00, 2, 6.0)
>
> insert into products (title, details, price, package_type_id, weight)
> values ('Title 5', '1000 x 20 x 20', 3.50, 1, 1.0)
>
> /* Create the PACKAGE TYPES */
>
> create table package_types
> (
> package_type_id int identity primary key,
> max_weight real,
> comments varchar(255)
> )
>
> /* Assign package types */
>
> insert into package_types (max_weight, comments) values (16, 'Flat')
> insert into package_types (max_weight, comments) values (12, 'Cube')
>
> /* Create a PURCHASE ORDER table */
>
> create table purchase_order
> (
> purchase_order_id int identity primary key,
> email_address varchar(255),
> item_count int default 0,
> packages int default 0,
> shipping smallmoney default 0
> )
>
> /* Add a purchase order */
>
> insert into purchase_order(email_address)
> values ('em...@address.com')
>
> /* Create an INVENTORY table */
>
> create table purchase_order_items
> (
> purchase_order_item_id int identity primary key,
> purchase_order_id int,
> product_id int,
> quantity int,
> )
>
> /* Assign some items to the purachse order */
>
> insert into purchase_order_items (purchase_order_id, product_id, quantity)
> values (1, 1, 4)
> insert into purchase_order_items (purchase_order_id, product_id, quantity)
> values (1, 2, 2)
> insert into purchase_order_items (purchase_order_id, product_id, quantity)
> values (1, 4, 1)
> insert into purchase_order_items (purchase_order_id, product_id, quantity)
> values (1, 5, 5)
>
> /* View the results */
>
> select * from products
> select * from package_types
> select * from purchase_order
> select * from purchase_order_items
>
> ------------ END OF SCRIPT ------------

What is the maximum package weight

"Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
news:1175082899.447081.251120@l77g2000hsb.googlegr oups.com...
On 28 Mar, 12:33, "Sean" <sean.anderson@[nospam]oakleafgroup.biz>
wrote:
>
> ------------ << snip >> ------------
>
>What is the maximum package weight
>


The maximum package weight is dependant on the type of package and is
defined in the "package_types" table in the "max_weight" column.