March 23, 2017 at 11:53 am
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?
This sounds like the problem may be much more complex than can be easily represented in a static query. Is there something within an Order that would associate the multiple stops together? What if one Order has multiple pickup and multiple drop-off stops? We'd need some additional rules around the data to be able to build a relationship between PU and SO stops. If there is guaranteed to only be one PU stop per Order, then it should be possible.
March 23, 2017 at 12:00 pm
I believe the customer we're sending this to the orders only have 1 pickup and 1 stop
March 23, 2017 at 12:09 pm
rmcguire 28206 - Thursday, March 23, 2017 11:26 AMcan 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
It's because 0 is shorthand for 1900-01-01 and -1 is shorthand for 1899-12-31. Since -1 is the end of the month before 0, this calculation will produce the end of the month before the current date.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply