Professional Web Applications Themes

Can someone help with query.... - MySQL

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 = ...

  1. #1

    Default 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 ('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 ------------





    Sean Guest

  2. #2

    Default Re: Can someone help with query....

    On 28 Mar, 12:33, "Sean" <sean.anderson[nospam]oakleafgroup.biz>
    wrote: 

    What is the maximum package weight

    Captain Guest

  3. #3

    Default Re: Can someone help with query....


    "Captain Paralytic" <com> wrote in message
    news:googlegroups.com...
    On 28 Mar, 12:33, "Sean" <sean.anderson[nospam]oakleafgroup.biz>
    wrote: 


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



    Sean Guest

Similar Threads

  1. Replies: 5
    Last Post: August 9th, 04:28 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139