Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: Mysql Logistics Query

    here is the new defined problem(forget about the old one):

    I m working on something called the analysis of deadhead route
    Deadhead route:
    a train, railroad car, airplane, truck, or other commercial vehicle while operating empty, as when returning to a terminal.For this i need to sort the data from the original data.

    Here is the original data set:

    create table Deadhead
    (origin varchar(50),
    destination varchar(50),
    tonnage float);


    Insert into Deadhead values

    ('Alabama','Alaska',788.88),
    ('Alabama','Texas',5665.88),
    ('Alabama','Arizona',6645),
    /*more Alabama data*/

    ('Alaska','Alabama',5546),
    ('Alaska','Texas',4556),
    /*more Alaska data*/


    ('Arizona','Alabama',73246),
    ('Arizona','Texas',273627)
    /*more Arizona data*/

    /* in between other data */

    ('Texas','Alabama',32756),
    ('Texas','Alaska',37257),
    ('Texas','Arizona',764236),
    /*more Texas data*/



    i.e. the data consist of tonnage values of transportation from 50 origins to 50 destinations arranged
    in an alphabetical order with respect to origin i.e. all tonnage values from Alabama as origin and to 50 other destinations comes first then tonnage values from Alaska to 50 other destination comes fsecond

    now here is what the output should look like

    'Alabama' , 'Alaska' , 788.88
    'Alaska' , 'Alabama', 5546
    'Alabama' , Arizona' , 6645
    'Arizona' , 'Alabama' , 73246
    'Alabama' , 'Texas' , 5665.88
    'Texas' , 'Alabama' , 32765


    i.e the output should be sorted in this manner and then further simple mathematical operators
    will be utilized on the above sorted data to calculate imbalance(difference) between a pair of origin and a destination tonnage value and then see whether that is significant to be considered as a deadhead route.
    For now just need the data to be sorted in the manner specified.

  2. #2
    Join Date
    Aug 2012
    Posts
    2

    Continuation

    This is sqo server counterpart of the code
    Can i get the mysql part??

    WITH CTE1 AS (
    SELECT TOP 100 PERCENT Origin=ISNULL(a.Origin, b.Destination)
    ,Destination=ISNULL(a.Destination, b.Origin)
    ,Tonnage=ISNULL(a.Tonnage, 0)
    FROM #Deadhead a
    FULL OUTER JOIN #Deadhead b
    ON a.Origin = b.Destination AND b.Origin = a.Destination
    ORDER BY ISNULL(a.Origin, b.Destination), ISNULL(a.Destination, b.Origin)
    ),
    CTE AS (
    SELECT c.Origin, c.Destination, c.Tonnage
    ,n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM CTE1 a
    CROSS APPLY CTE1 b
    CROSS APPLY (
    VALUES (a.Origin, a.Destination, a.Tonnage)
    ,(b.Origin, b.Destination, b.Tonnage)) c(Origin, Destination, Tonnage)
    WHERE (a.Origin = b.Destination AND b.Origin = a.Destination)
    )
    SELECT Origin, Destination, Tonnage
    FROM (
    SELECT TOP ((SELECT COUNT(*) FROM CTE1)
    ) Origin, Destination, Tonnage
    FROM CTE a
    WHERE EXISTS (
    SELECT 1
    FROM CTE b
    WHERE b.n < a.n AND b.Origin = a.Origin AND b.Destination = a.Destination)) b
    WHERE Tonnage <> 0

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    now here is what the output should look like

    'Alabama' , 'Alaska' , 788.88
    'Alaska' , 'Alabama', 5546
    'Alabama' , Arizona' , 6645
    'Arizona' , 'Alabama' , 73246
    'Alabama' , 'Texas' , 5665.88
    'Texas' , 'Alabama' , 32765
    Why were not the following rows included from the sample data?
    'Alaska' , 'Texas' , 4556
    'Texas' , 'Alaska' , 37257
    'Arizona' , 'Texas' , 273627
    'Texas' , 'Arizona' , 764236

    If those rows are also included, try ...
    Code:
    SELECT origin , destination , tonnage
     FROM  Deadhead
     ORDER BY
           LEAST   (origin , destination)
         , GREATEST(origin , destination)
         , origin
    ;

Posting Permissions

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