displaying 2 values when field same

  • I have a field, stop.city_name, and it can be 2 different locations based on a stop.stop_type...so I'm trying to display

    when stop.stop_type = 'PU' then city_name    (to show the pickup city)

    when stop.stop_type = 'SO' then city_name (to show the delivery city)

    I'm trying to it using a case statement but can seem to get it to display both values

  • rmcguire 28206 - Wednesday, March 22, 2017 11:47 AM

    I have a field, stop.city_name, and it can be 2 different locations based on a stop.stop_type...so I'm trying to display

    when stop.stop_type = 'PU' then city_name    (to show the pickup city)

    when stop.stop_type = 'SO' then city_name (to show the delivery city)

    I'm trying to it using a case statement but can seem to get it to display both values

    care to share your complete code that is causing you problems?
    what do you mean by "both" values....examples would help here me thinks

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • here is a link on how to post questions so that everyone understands and help

    https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • select orders.rate, stop.actual_departure, stop.city_name, stop.state, stop.order_id ,
    CASE when stop.stop_type = 'PU' then city_name
        end as pickup

    from orders
    RIGHT OUTER JOIN stop ON orders.id = stop.order_id
    where orders.equipment_type_id = 'V' and orders.customer_id = 'BEACCOOH' and orders.status = 'D'

  • rmcguire 28206 - Wednesday, March 22, 2017 11:55 AM

    select orders.rate, stop.actual_departure, stop.city_name, stop.state, stop.order_id ,
    CASE when stop.stop_type = 'PU' then city_name
        end as pickup

    from orders
    RIGHT OUTER JOIN stop ON orders.id = stop.order_id
    where orders.equipment_type_id = 'V' and orders.customer_id = 'BEACCOOH' and orders.status = 'D'

    is it as simple as adding another case statement......

    SELECT orders.rate,
       stop.actual_departure,
       stop.city_name,
       stop.state,
       stop.order_id,
       CASE
        WHEN stop.stop_type = 'PU'
        THEN city_name
       END AS pickup,
         CASE
        WHEN stop.stop_type = 'SO'
        THEN city_name
       END AS delivery

    FROM orders
      RIGHT OUTER JOIN stop ON orders.id = stop.order_id
    WHERE orders.equipment_type_id = 'V'
      AND orders.customer_id = 'BEACCOOH'
      AND orders.status = 'D';

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • I don't believe I'm going to be able to provide the data readily, I'm basically just trying to find out if there is a way to display the pickup and delivery city even if they are the same field, city_name. My join I believe is correct, and I think this can be done in a case statement but not sure how to write it properly dependant on the stop_type, being 'PU' for pickup and 'SO' for delivery

  • rmcguire 28206 - Wednesday, March 22, 2017 12:02 PM

    I don't believe I'm going to be able to provide the data readily, I'm basically just trying to find out if there is a way to display the pickup and delivery city even if they are the same field, city_name. My join I believe is correct, and I think this can be done in a case statement but not sure how to write it properly dependant on the stop_type, being 'PU' for pickup and 'SO' for delivery

    think our posts are crossing over...see my code above

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • so that displays correctly as far as showing all of the data, thank you for that, but perhaps my join isn't correct as  for each order there are 2 stops, ex. orders.id = '0935800' is displaying twice one for the pickup and one for the delivery, would it be possible to have one record display for each orders.id?

  • rmcguire 28206 - Wednesday, March 22, 2017 12:13 PM

    so that displays correctly as far as showing all of the data, thank you for that, but perhaps my join isn't correct as  for each order there are 2 stops, ex. orders.id = '0935800' is displaying twice one for the pickup and one for the delivery, would it be possible to have one record display for each orders.id?

    maybe a "Group By".....dunno...cant see your data and seems you a bit reluctant to set up any sample data....pity!

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Here are 2 .xlsx files one for our orders and one for the stops, when you look through the stops you'll see there are 2 order_id's for each which match the corresponding id in orders but then when running my script the data is displayed for each order on 2 lines

  • rmcguire 28206 - Wednesday, March 22, 2017 12:40 PM

    Here are 2 .xlsx files one for our orders and one for the stops, when you look through the stops you'll see there are 2 order_id's for each which match the corresponding id in orders but then when running my script the data is displayed for each order on 2 lines

    and your expected results please

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Here is an example on order 0940136

  • I would like you to read this please...as previously suggested  https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
    posting excel spreadsheets and expecting others, upon whom you are requesting help, to do the necessary work to convert to code that can be easily pasted into SSMS, is (in mind at least) sheer laziness.!

    that said (RANT OVER)
    does this help ??

    USE TEMPDB
    GO

    CREATE TABLE your_orders(
     id VARCHAR(7)
    ,rate NUMERIC(5,1)
    );
    INSERT INTO your_orders(id,rate) VALUES ('0940136',650);
    INSERT INTO your_orders(id,rate) VALUES ('0935800',712.8);
    INSERT INTO your_orders(id,rate) VALUES ('0939710',650);

    CREATE TABLE your_stop(
     order_id   VARCHAR(7)
    ,actual_departure DATETIME
    ,city_name   VARCHAR(12)
    ,stop_type   VARCHAR(2)
    );
    INSERT INTO your_stop(order_id,actual_departure,city_name,stop_type) VALUES ('0935800','2017-01-11 04:45:00','OMAHA','PU');
    INSERT INTO your_stop(order_id,actual_departure,city_name,stop_type) VALUES ('0939710','2017-02-15 01:30:00','INDIANAPOLIS','SO');
    INSERT INTO your_stop(order_id,actual_departure,city_name,stop_type) VALUES ('0935800','2017-01-12 09:00:00','HUDSON','SO');
    INSERT INTO your_stop(order_id,actual_departure,city_name,stop_type) VALUES ('0939710','2017-02-14 01:52:00','KANSAS CITY','PU');
    INSERT INTO your_stop(order_id,actual_departure,city_name,stop_type) VALUES ('0940136','2017-02-16 00:15:00','KANSAS CITY','PU');
    INSERT INTO your_stop(order_id,actual_departure,city_name,stop_type) VALUES ('0940136','2017-02-17 02:16:00','INDIANAPOLIS','SO');

    SELECT o.id,
       o.rate,
       MIN(s.actual_departure) AS ACT_DEP,
       MAX(CASE
         WHEN s.stop_type = 'PU'
         THEN s.city_name
        END) AS pickup,
       MAX(CASE
         WHEN s.stop_type = 'SO'
         THEN s.city_name
        END) AS delivery
    FROM your_orders AS o
      INNER JOIN your_stop AS s ON o.id = s.order_id
    GROUP BY o.id,
       o.rate
    ORDER BY o.id;

    DROP TABLE your_orders
    DROP TABLE your_stop

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • sorry about that I will do that in the future.....what was provided was just a small sample of data, I think what you wrote as below works, I just need to figure out a way to display the orders on one row for the pickup and delivery location, with our data we won't be able to insert the data in manually as I believe you are suggesting above? Sorry If I misunderstand....thanks again

    SELECT orders.rate,
     stop.actual_departure,
     stop.city_name,
     stop.state,
     orders.id,
     CASE
      WHEN stop.stop_type = 'PU'
      THEN city_name
     END AS pickup,
      CASE
      WHEN stop.stop_type = 'SO'
      THEN city_name
     END AS delivery

    FROM orders
     RIGHT OUTER JOIN stop ON orders.id = stop.order_id
    WHERE orders.equipment_type_id = 'V'
    AND orders.customer_id = 'BEACCOOH'
    AND orders.status = 'D';

  •  with our data we won't be able to insert the data in manually as I believe you are suggesting above

    not sure what you mean?
    the example created some sample data for you,,,,,,,,,,,,,,,,,

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 15 posts - 1 through 15 (of 32 total)

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