February 19, 2015 at 1:28 pm
Hi All, I need help figuring out what I'm doing wrong in this code.
I built a query that brings in 'Discounts' (bolded) to the Order detail by using the bolded syntax below. I started off by running the query without the bolded lines and got exactly what I was looking for but without the ‘Discount’ column. When I tried to add the ‘Discount’ into the query, it duplicated several order lines. Although total ‘Discount’ column ties out to the total amount expected in that column, ‘Total Charges’ are now several times higher than before.
For example, I get 75 records when I run without the bolded syntax and I get several hundred results back when adding back in the bolded syntax when i should still be getting 75 records, just with an additional column ‘PTL Discount’ subtotaled.
My question is, how to I introduce a new select or join from another table without duplicating the original data?
Thanks in advance!
select
first_stop.actual_departure ‘Start'
, last_stop.actual_departure 'End'
, last_stop.city_name 'End city'
, last_stop.state 'End state'
, last_stop.zip_code 'End zip'
, first_stop.city_name ‘Start city'
, first_stop.state 'Start state'
, first_stop.zip_code 'Start zip'
, datediff (day, last_stop.actual_departure, first_stop.actual_departure) 'Days in Transit'
, orders.id 'Order number'
, case
when (sales.dis = 'a' or sales.dis = 'b' or sales.dis = 'c')
then sales.amount
else 0
end 'Discounts'
, orders.total_charge 'Total charges'
from
orders left outer join stop last_stop on last_stop_id = last_stop.id
left outer join stop first_stop on first_stop_id = first_stop.id
left outer join customer on customer_id = customer.id
left outer join sales on orders.id = sales.reference
where
orders.status = 'D'
and (first_stop.actual_departure >= {ts '2015-01-01 00:00:00'} and first_stop.actual_departure <= {ts '2015-01-31 23:59:59'})
February 19, 2015 at 1:53 pm
Is sales.Dis ever NULL?
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
February 19, 2015 at 2:21 pm
What about this?
select
first_stop.actual_departure 'Start'
, last_stop.actual_departure 'End'
, last_stop.city_name 'End city'
, last_stop.state 'End state'
, last_stop.zip_code 'End zip'
, first_stop.city_name 'Start city'
, first_stop.state 'Start state'
, first_stop.zip_code 'Start zip'
, datediff (day, last_stop.actual_departure, first_stop.actual_departure) 'Days in Transit'
, orders.id 'Order number'
, ISNULL( s.Discounts, 0) 'Discounts'
, orders.total_charge 'Total charges'
from
orders left outer join stop last_stop on last_stop_id = last_stop.id
left outer join stop first_stop on first_stop_id = first_stop.id
left outer join customer on customer_id = customer.id
OUTER APPLY( SELECT SUM(amount) AS Discounts FROM sales
WHERE orders.id = sales.reference
AND sales.dis IN( 'a', 'b', 'c')
GROUP BY sales.reference) s
where
orders.status = 'D'
and (first_stop.actual_departure >= {ts '2015-01-01 00:00:00'} and first_stop.actual_departure <= {ts '2015-01-31 23:59:59'})
February 19, 2015 at 3:47 pm
Sigerson (2/19/2015)
Is sales.Dis ever NULL?
Never Null.
February 19, 2015 at 4:57 pm
Luis Cazares (2/19/2015)
What about this?
select
first_stop.actual_departure 'Start'
, last_stop.actual_departure 'End'
, last_stop.city_name 'End city'
, last_stop.state 'End state'
, last_stop.zip_code 'End zip'
, first_stop.city_name 'Start city'
, first_stop.state 'Start state'
, first_stop.zip_code 'Start zip'
, datediff (day, last_stop.actual_departure, first_stop.actual_departure) 'Days in Transit'
, orders.id 'Order number'
, ISNULL( s.Discounts, 0) 'Discounts'
, orders.total_charge 'Total charges'
from
orders left outer join stop last_stop on last_stop_id = last_stop.id
left outer join stop first_stop on first_stop_id = first_stop.id
left outer join customer on customer_id = customer.id
OUTER APPLY( SELECT SUM(amount) AS Discounts FROM sales
WHERE orders.id = sales.reference
AND sales.dis IN( 'a', 'b', 'c')
GROUP BY sales.reference) s
where
orders.status = 'D'
and (first_stop.actual_departure >= {ts '2015-01-01 00:00:00'} and first_stop.actual_departure <= {ts '2015-01-31 23:59:59'})
it's coming back with sales.amount could not be bound.... hmmmmm
February 19, 2015 at 5:21 pm
Do you have anything else in the code?
Did you run the exact same code I posted?
Note that I changed the JOIN to an APPLY and the column expression for 'Discounts'.
February 19, 2015 at 5:56 pm
Luis Cazares (2/19/2015)
Do you have anything else in the code?Did you run the exact same code I posted?
Note that I changed the JOIN to an APPLY and the column expression for 'Discounts'.
Nothing else in the code.
A few changes assuming you didn't complete the spellings completely:
On the ISNULL line, I put sales.discount instead of s.discount
On the Outer apply statement, I put sales.discount instead of sales.dis
And what does the 's' mean at the end of the outer apply statement? Should that be sales or just s?
February 19, 2015 at 6:01 pm
sqlnoob2015 (2/19/2015)
Luis Cazares (2/19/2015)
Do you have anything else in the code?Did you run the exact same code I posted?
Note that I changed the JOIN to an APPLY and the column expression for 'Discounts'.
Nothing else in the code.
A few changes assuming you didn't complete the spellings completely:
On the ISNULL line, I put sales.discount instead of s.discount
On the Outer apply statement, I put sales.discount instead of sales.dis
And what does the 's' mean at the end of the outer apply statement? Should that be sales or just s?
oops changed it back to dis. still error though
February 19, 2015 at 6:07 pm
s is an alias for the resultset from the apply. It can be anything but I prefer it to be something that could be significant. On the ISNULL column you need to use that alias and the name of the column defined in the APPLY subquery
You had sales.dis as the column used in your CASE.
February 19, 2015 at 6:27 pm
Luis Cazares (2/19/2015)
s is an alias for the resultset from the apply. It can be anything but I prefer it to be something that could be significant. On the ISNULL column you need to use that alias and the name of the column defined in the APPLY subqueryYou had sales.dis as the column used in your CASE.
OK, i finally got it to run. Thanks for your response and explanation. That makes since.
However, now I do not have any totals in the Discounts column. It's all zeros. I know there are values because when i run a select statement with just the discounts, I get values.
February 19, 2015 at 6:48 pm
discounts is basically coming back null.
February 20, 2015 at 8:19 am
Does this return any rows?
select *
from orders o
JOIN stop first_stop on o.first_stop_id = first_stop.id
JOIN sales s ON o.id = s.reference
WHERE s.dis IN( 'a', 'b', 'c')
AND o.status = 'D'
AND first_stop.actual_departure >= {ts '2015-01-01 00:00:00'}
AND first_stop.actual_departure <= {ts '2015-01-31 23:59:59'}
If it does, the complete query should return discounts. If it doesn't, you don't have data available.
February 23, 2015 at 5:18 pm
Luis Cazares (2/20/2015)
Does this return any rows?
select *
from orders o
JOIN stop first_stop on o.first_stop_id = first_stop.id
JOIN sales s ON o.id = s.reference
WHERE s.dis IN( 'a', 'b', 'c')
AND o.status = 'D'
AND first_stop.actual_departure >= {ts '2015-01-01 00:00:00'}
AND first_stop.actual_departure <= {ts '2015-01-31 23:59:59'}
If it does, the complete query should return discounts. If it doesn't, you don't have data available.
I got it figured out. Thanks a lot for your help on this!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply