March 22, 2017 at 2:18 pm
with what I'm doing with our software, we're a transportation brokerage and with their 'Document Designer' aka form builder using sql, I cannot use CREATE TABLE
March 22, 2017 at 2:32 pm
The CREATE TABLE is in there to create the sample data. PERIOD. Since you would be using the live data, you don't need to create the sample data, so you don't need the CREATE TABLE.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 22, 2017 at 2:39 pm
but from what is listed above how would I display my data on one row so it would match my example?
Basically for every two stops there is one order but from the above query Its display the data for each order on 2 rows
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 23, 2017 at 1:53 am
I think you will need to join on the stop table twice once for the stop_type of PU and once for the stop_type of SO. Something like below:
SELECT o.rate,
sPU.actual_departure,
sPU.city_name,
sPU.state,
o.id,
PickUpCIty = sPU.City_Name,
Delivery =sSO.city_name
FROM orders o
RIGHT OUTER JOIN stop sPU ON o.id = sPU.order_id AND sPU.Stop_Type = 'PU' -- Pickup CIty
RIGHT OUTER JOIN stop sSO ON o.id = sSO.order_id AND sSO.Stop_Type = 'SO' -- Delivery City
WHERE o.equipment_type_id = 'V'
AND o.customer_id = 'BEACCOOH'
AND o.status = 'D';
You may need to make one or both of the joins to stop left joins.
March 23, 2017 at 6:33 am
So that appeared to work!! Thank you so much, but I'm not understanding where sPU and sSO and defined?
March 23, 2017 at 6:45 am
sPU and sSO are aliases for the table Stop so that we can link to this table twice once (alias of sPU) to get the value for the Pickup City name and once (alias sSO) to get the value for the Delivery City name.
March 23, 2017 at 6:47 am
how does it know that sPU and sSO are off of the stop table?
March 23, 2017 at 7:01 am
From the code provided, after the table name is the alias so below the table stop is following by sPU which is now the alias which will .
RIGHT OUTER JOIN stop sPU On o.Id = sPU.order_id AND sPU.Stop_Type = 'PU'
So this is saying that once instance of the table stop is associated to the alias sPU and this links to the order table using the alias o but only where this instance of the stop table (alias sPU) has a stop_type = 'PU' so this is filtering on the Stop table only for the Stop_Type of Pickup.
The second instance of the Stop table has an alias of sSO but filters for those values which have a Stop_Type of SO. Again this links to the Orders table (using alias O)
RIGHT OUTER JOIN stop sSO On o.Id = sSO.order_id AND sSO.Stop_Type = 'SO'
So in the end you get one row as there is one order but both of the City Names from the Stop table. If your data only has one record in the stop table you would require Left Joins otherwise no data will br returned.
This link may help you - Using Table Aliases
March 23, 2017 at 10:39 am
so now I'm needing based off our scheduler to send the report out based of the previous month's actual departure
so for February it would be what is below, but is there a way to write this so the dates roll each month?
SELECT o.rate,
sPU.actual_departure,
sPU.city_name,
sPU.state,
o.id,
PickUpCIty = sPU.City_Name,
Delivery =sSO.city_name
FROM orders o
RIGHT OUTER JOIN stop sPU ON o.id = sPU.order_id AND sPU.Stop_Type = 'PU' -- Pickup CIty
RIGHT OUTER JOIN stop sSO ON o.id = sSO.order_id AND sSO.Stop_Type = 'SO' -- Delivery City
WHERE o.equipment_type_id = 'V'
AND o.customer_id = 'BEACCOOH'
AND o.status = 'D'
AND sPU.actual_departure > '2/1/2017'
AND sPU.actual_departure < '2/28/2017';
March 23, 2017 at 10:45 am
rmcguire 28206 - Thursday, March 23, 2017 10:39 AMso now I'm needing based off our scheduler to send the report out based of the previous month's actual departureso for February it would be what is below, but is there a way to write this so the dates roll each month?
SELECT o.rate,
sPU.actual_departure,
sPU.city_name,
sPU.state,
o.id,
PickUpCIty = sPU.City_Name,
Delivery =sSO.city_name
FROM orders o
RIGHT OUTER JOIN stop sPU ON o.id = sPU.order_id AND sPU.Stop_Type = 'PU' -- Pickup CIty
RIGHT OUTER JOIN stop sSO ON o.id = sSO.order_id AND sSO.Stop_Type = 'SO' -- Delivery City
WHERE o.equipment_type_id = 'V'
AND o.customer_id = 'BEACCOOH'
AND o.status = 'D'
AND sPU.actual_departure > '2/1/2017'
AND sPU.actual_departure < '2/28/2017';
see this article.....http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 23, 2017 at 10:59 am
so I believe this should work
AND sPU.actual_departure > DateAdd(M, -1, GetDate());
March 23, 2017 at 11:07 am
rmcguire 28206 - Thursday, March 23, 2017 10:59 AMso I believe this should workAND sPU.actual_departure > DateAdd(M, -1, GetDate());
you mention "dates roll each month"....what are you expecting...the first and last date of the current month/ last day of previous month/first day of next month ...
not sure the following gives you what I think you are asking for....maybe/maybe not?
select DateAdd(M, -1, GetDate())
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 23, 2017 at 11:12 am
If I'm understanding correctly GetDate() is for the current date and the above would pull anything 1 month before the current date
select DateAdd(M, -1, GetDate())
my scheduler is set to run on the first of every month so when this runs it should in theory grab anything which has the
sPU.actual_departure > DateAdd(M, -1, GetDate());
March 23, 2017 at 11:21 am
rmcguire 28206 - Thursday, March 23, 2017 11:12 AMIf I'm understanding correctly GetDate() is for the current date and the above would pull anything 1 month before the current dateselect DateAdd(M, -1, GetDate())
my scheduler is set to run on the first of every month so when this runs it should in theory grab anything which has the
sPU.actual_departure > DateAdd(M, -1, GetDate());
if I recall, the sample spreadsheets you provided earlier in this thread had dateTIME datatypes......?
With your current code ...what are you going to do when the scheduler falls over and you have to rerun the following day or rerun mid month?
suggest you use this construct
sPU.actual_departure >= dateadd(mm, datediff(mm, 0, getdate()), 0) --- note the ">="
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 23, 2017 at 11:26 am
can you explain why in:
sPU.actual_departure >= dateadd(mm, datediff(mm, 0, getdate()), 0)
you would write as
sPU.actual_departure >= dateadd(mm, datediff(mm, 0, getdate()), -1) to get the previous month?
thank you for the help on this again
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply