March 22, 2017 at 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
March 22, 2017 at 11:49 am
rmcguire 28206 - Wednesday, March 22, 2017 11:47 AMI have a field, stop.city_name, and it can be 2 different locations based on a stop.stop_type...so I'm trying to displaywhen 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
March 22, 2017 at 11:54 am
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
March 22, 2017 at 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'
March 22, 2017 at 12:01 pm
rmcguire 28206 - Wednesday, March 22, 2017 11:55 AMselect orders.rate, stop.actual_departure, stop.city_name, stop.state, stop.order_id ,
CASE when stop.stop_type = 'PU' then city_name
end as pickupfrom 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
March 22, 2017 at 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
March 22, 2017 at 12:05 pm
rmcguire 28206 - Wednesday, March 22, 2017 12:02 PMI 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
March 22, 2017 at 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?
March 22, 2017 at 12:20 pm
rmcguire 28206 - Wednesday, March 22, 2017 12:13 PMso 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
March 22, 2017 at 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
March 22, 2017 at 12:46 pm
rmcguire 28206 - Wednesday, March 22, 2017 12:40 PMHere 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
March 22, 2017 at 12:55 pm
Here is an example on order 0940136
March 22, 2017 at 1:30 pm
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
March 22, 2017 at 1:40 pm
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';
March 22, 2017 at 2:10 pm
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