Post reply

shipping system - stored procedure - self-referencing table

  • March 6, 2006 at 12:51 am

    #113607

    i'm working with a shipping system.

    The basic table structure:

    id place_from place_to cntr_type price currency charge_type
    1 mypkg deham 20gp 800 usd ocb
    2 mypkg deham 40gp 1600 usd ocb
    3 mypkg nlrtm 20gp 800 usd ocb
    4 mypkg nlrtm 40gp 1600 usd ocb
    5 deham deabc 20gp 150 usd dib
    6 deham deabc 40gp 300 usd dib
    7 deham noosl 20gp 200 usd ddu
    8 deham noosl 40gp 400 usd ddu
    9 noosl noabc 20gp 100 usd dib
    10 noosl noabc 40gp 200 usd dib
    11 nlrtm nlabc 20gp 200 usd dib
    12 nlrtm nlabc 40gp 400 usd dib

    This table shows the price for shipping different type of containers from one location to another.

    I want to create a stored procedure that is able to return the result for shipment of specific cntr type from an origin to a destination. For example

    1. shipment of 20gp container from mypkg to noabc will be:

    place_from place_to cntr_type price currency charge_type
    mypkg deham 20gp 800 usd ocb
    deham noosl 20gp 200 usd ddu
    noosl noabc 20gp 200 usd dib

    2. shipment of 20gp from mypkg to nlabc

    place_from place_to cntr_type price currency charge_type
    mypkg nlrtm 20gp 800 usd ocb
    nlrtm nlabc 20gp 200 usd dib

    3. shipment from mypkg to deham

    place_from place_to cntr_type price currency charge_type
    mypkg deham 20gp 800 usd ocb
    mypkg deham 40gp 1600 usd ocb

    4. shipment from mypkg to noosl

    place_from place_to cntr_type price currency charge_type
    mypkg deham 20gp 800 usd ocb
    deham noosl 20gp 200 usd ddu
    mypkg deham 40gp 1600 usd ocb
    deham noosl 40gp 400 usd ddu

    Pls help.

  • March 6, 2006 at 3:43 am

    #624834

    anybody can help?

  • March 6, 2006 at 2:48 pm

    #625055

    Is each example beside #4 (two packages) showing a package along it entire voyage with all stops?

    #1 shows one package with two stops and then its final destination?

  • March 6, 2006 at 9:33 pm

    #625106

    enthusiast,

    u r right.

    The stored proc would be something like:

    CREATE PROCEDURE GetFreight

      @origin char(5),

      @destination char(5),

      @cntr_type char(4)

    AS

      ...

    Result returns based on the parameters, example

    EXEC GetFreight mypkg, noabc, 20gp

    will get the result as example #1.

    EXEC GetFreight mypkg, nlabc, 20gp

    will get the result as example #2.

    @cntr_type may be optional and thus without supplying it, the result will be as example #3 and #4

    EXEC GetFreight mypkg, deham

    EXEC GetFreight mypkg, noosl

    Pls assist.

  • March 7, 2006 at 2:47 am

    #625127

    How can we solve this if there is more than one route from origin to destination? Do you have a way of specifying the route?

    I.e. do you want the route with the fewest stops? Or the cheapest route? Or is there always at most one route from origin to destination?

     

  • March 7, 2006 at 5:53 am

    #625148

    Ang,

    That was a fun problem 

    Here's something for starters for you to play with. It should give all possible journeys - but might need extending according to your needs...

    --This SQL script is safe to run

    --Create data

    set nocount on

    declare @routes table (id int, place_from varchar(5), place_to varchar(5), cntr_type varchar(5), price int, currency varchar(3), charge_type varchar(3))

    insert into @routes

          select  1, 'mypkg', 'deham', '20gp', 800,  'usd', 'ocb'

    union select  2, 'mypkg', 'deham', '40gp', 1600, 'usd', 'ocb'

    union select  3, 'mypkg', 'nlrtm', '20gp', 800,  'usd', 'ocb'

    union select  4, 'mypkg', 'nlrtm', '40gp', 1600, 'usd', 'ocb'

    union select  5, 'deham', 'deabc', '20gp', 150,  'usd', 'dib'

    union select  6, 'deham', 'deabc', '40gp', 300,  'usd', 'dib'

    union select  7, 'deham', 'noosl', '20gp', 200,  'usd', 'ddu'

    union select  8, 'deham', 'noosl', '40gp', 400,  'usd', 'ddu'

    union select  9, 'noosl', 'noabc', '20gp', 100,  'usd', 'dib'

    union select 10, 'noosl', 'noabc', '40gp', 200,  'usd', 'dib'

    union select 11, 'nlrtm', 'nlabc', '20gp', 200,  'usd', 'dib'

    union select 12, 'nlrtm', 'nlabc', '40gp', 400,  'usd', 'dib'

    union select 13, 'mypkg', 'noabc', '20gp', 400,  'usd', 'dib' --extra data

    union select 14, 'noabc', 'mypkg', '20gp', 400,  'usd', 'dib' --extra data

    union select 15, 'mypkg', 'noosl', '20gp', 400,  'usd', 'dib' --extra data

    --Declare and set variables

    declare @cntr_type  varchar(20)

    declare @place_from varchar(5)

    declare @place_to   varchar(5)

    set @cntr_type  = '20gp'

    set @place_from = 'mypkg'

    set @place_to   = 'noosl'

    --Get possible journeys

    declare @journeys table (id int identity, id1 int, id2 int, id3 int, id4 int)

    insert into @journeys

    select

        t1.id, t2.id, t3.id, t4.id

    from

        @routes t1

        left outer join @routes t2 on t1.place_to = t2.place_from and t1.cntr_type = t2.cntr_type and not t2.place_from = @place_to and not t2.place_to = @place_from

        left outer join @routes t3 on t2.place_to = t3.place_from and t2.cntr_type = t3.cntr_type and not t3.place_from = @place_to and not t3.place_to = @place_from

        left outer join @routes t4 on t3.place_to = t4.place_from and t3.cntr_type = t4.cntr_type and not t4.place_from = @place_to and not t4.place_to = @place_from

    where

        t1.cntr_type = case when @cntr_type is null then t1.cntr_type else @cntr_type end

        and t1.place_from = @place_from

        and @place_to in (t1.place_to, t2.place_to, t3.place_to, t4.place_to)

    --Get possible journeys in the format we need

    declare @journeysTransposed table (routeOrder int, journeyId int, routeId int)

    insert into @journeysTransposed

          select 1, id, id1 from @journeys

    union select 2, id, id2 from @journeys

    union select 3, id, id3 from @journeys

    union select 4, id, id4 from @journeys

    --Select the routes which make up the possible journeys

    set nocount off

    select journeyId, r.* from @journeysTransposed jt inner join @routes r on jt.routeId = r.id order by journeyId, jt.routeOrder

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • March 8, 2006 at 10:35 pm

    #625588

    hi addict, sorry for the late reply.

    i'm thinking also to add a routing table to store the possible routing from origin to dest.

    so i hv to search this routing table for the complete route from origin to dest, which may be more than one. And with the results i'll need to go thru the price table to get the pricing.

    i'm not very familiar with programming in stored proc, so i'm stuck on how to use the results from the routing table to get the result i want from the pricing table.

  • March 8, 2006 at 11:41 pm

    #625589

    First of all, you should really keep the pricing away from the routing. Your business rules might change ... Imagine, pricing will be based on [total distance] * some-amount + [number of waypoints] * some-amount - discount (based on overall sum ...whatever).

    Such a slight change will screw up your work if you don't plan for.

    table [location]

    1. noosl

    2. deham

    ...

    table [route]

    route1:id1:id2

    route2:id1:id4

    table [routeattribute]

    route1: gp20: 800 usd

    route2: gp20: 600 usd

    route2: gp40: 777 usd

    With routeattribute you get all the flexibility you need without duplicating routing information itself (+integrity and 1. nf)

    You still have to decide whether you take for granted that any route has a back-route with the same attributes or whether for some reasons the backroute may have different attributes [as often happens]. If this is the case as real world circumstances imply you should store deham-noosl as well as noosl-deham as a seperate route.

    This design fits even most strange requirements. Say for example you're trying to ship nuclear material to Iran. You'll first want to leave for Ecuador, then Libya, later Irak ... based on demands of avoiding prosecution - in this case you may want to add appropriate attribute - but - this time NOT for the route, but for the waypoint: [iswaypointsafe]-attribute. With your original design you'll have to add such an attribute to ALL routes which contain a specific waypoint. A more real requirement may be that loading at different waypoints may be part of your pricing and has to be consiered as well.


    _/_/_/ paramind _/_/_/

  • March 9, 2006 at 2:26 am

    #625611

    ang, I still don't know what you want...

    Do you have a complete route from origin to destination (including all intermediate stops), and want to calculate the price?

    Or do you only have origin and destination and want to find the cheapest route?

    paramind has some good comments on table structure, I think you should look into that...

    "Enthusiast" and "Addict" are labels that this site attaches to you based on your number of posts... Directly above this we have the unique name of the person who wrote the post (the one with the link), and this is what we normally use when we reference another writer

     

     

  • March 9, 2006 at 11:36 pm

    #625850

    jesper (not addict :rolleyes... as my 'label' implies, i'm really a newbie here... first time posting

    ok. lets continue. actually the route from the origin to dest is one way only and fixed with the exception that one or at most two intermediate stops maybe different. for example

    mypkg -> deham -> noosl -> noabc

    OR

    mypkg -> nlrtm -> noosl -> noabc

    mypkg is the origin

    deham or nltrm is the transshipment port (VIA port)

    noosl is the port of discharge (POD)

    noabc is the destination

    The route from POD to destination is always fixed. It's just that the VIA port may differ.

    I'd like to know the complete route from the origin to the destination and show the breakdown of the price. In the above example in this reply, there will be two results when i query the pricing from mypkg to noabc.

    As with paramind and jesper suggestion earlier, i am considering adding a routing table that will store the all the stop point from origin to dest. This table will allow me to check whether we can put a shipment from one origin to one dest.

    But i'm having problem on how the structure of the table should be and how the records can be used to get the price from the pricing table when usually we are only provided with the origin port code and the dest port code.

    This whole problem of getting the correct tables and stored proc has been messing me for some times . Had in many occasions tried many different table structures or separate the tables but it seems like it'll make the logic in the stored proc more complicated . So I came to this point to create the table (as shown in my first posting) that combine everything together.

    and oh btw, the sql given by RyanRandall, it's give me the results i want as mentioned in my first post.

    but I still appreaciate all suggestions and i'll try all of them to see if they fit into my context especially now i want to add a routing table.

  • March 10, 2006 at 2:30 am

    #625875

    >This whole problem of getting the correct tables and stored proc has been messing me for some times . Had in many occasions tried many different table structures or separate the tables but it seems like it'll make the logic in the stored proc more complicated<

    Ang ... one question. What is the really ingenious thing about mathematics? We don't - at least I don't - store all the numbers we ever need in a table just to pick them occassionally. Neither do we store all equations we ever need. Why? Because it's easy to assemble them on the fly, once you've understood the concept behind. It's well worth.

    For instance I never carry lighted cigarettes in my pockets, I prefer a box of cigarettes in one of them and a lighter in the other one. When necessary ... I do a select and join them. That's very simple and - moreover - less painful.

    ....

    >But i'm having problem on how the structure of the table should be and how the records can be used to get the price from the pricing table when usually we are only provided with the origin port code and the dest port code.<

    Give it a try. I'll really help you. What question [in english ] was not resolvable for you with the n-table structure?

     


    _/_/_/ paramind _/_/_/

Viewing 11 posts - 1 through 10 (of 10 total)

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