August 29, 2008 at 9:58 am
Hello I am running the following 3 seperate queries:
USE DB
SELECT
sum(T_OrderLine.Quantity) AS SoldQty,
sum(T_OrderLine.ExtendedPrice) AS TotalSoldValue,
sum(T_OrderLine.QuantityShipped) AS ShippedQty,
sum(T_OrderLine.QuantityShipped * T_OrderLine.ExtendedPrice) AS TotalShippedValue,
sum(T_ReturnLines.QuantityReturned) AS ReturnedQty,
sum(T_Returns.ReturnTotal) AS TotalReturnedValue
FROM T_OrderLine LEFT JOIN
T_Returns ON T_OrderLine.OrderID = T_Returns.OrderID INNER JOIN
T_ReturnLines ON T_Returns.ReturnID = T_ReturnLines.ReturnId INNER JOIN
T_ReturnTypes ON T_Returns.ReturnTypeID = T_ReturnTypes.ReturnTypeID
WHERE (T_Returns.ReturnDate BETWEEN '2008-08-23' AND '2008-08-30')
go
select sum(T_OrderLine.QuantityShipped) as qty_shipped
from T_OrderLine
WHERE (T_OrderLine.ShipDate BETWEEN '2008-08-23' AND '2008-08-30')
go
select sum(T_ReturnLines.QuantityReturned) as qty_returned
from T_ReturnLines inner join T_Returns on T_ReturnLines.ReturnId = T_Returns.ReturnID
WHERE (T_Returns.ReturnDate BETWEEN '2008-08-23' AND '2008-08-30')
----
SoldQty TotalSoldValue ShippedQty TotalShippedValue ReturnedQty TotalReturnedValue
----------- --------------------- ----------- --------------------- ----------- ---------------------
910 22587.9819 906 24320.0219 916 77160.394
(1 row(s) affected)
qty_shipped
-----------
5617
(1 row(s) affected)
qty_returned
------------
209
I am trying to join the second and third query together to get one result set, which will have
qty_shipped, qty_shipped_value, qty_returned, qty_returned_value, qty_returned_type(exchange or refund).
can anyone help?
August 29, 2008 at 12:20 pm
Do you mean you want 1 query to return all your results or 2 queries (queries 2 and 3 joined)?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 29, 2008 at 1:14 pm
As written, the WHERE restrictions are causing any left outer joined rows to be removed from the result. Try this:
FROM T_OrderLine
LEFT OUTER JOIN
T_Returns
ON T_OrderLine.OrderID = T_Returns.OrderID
INNER JOIN
T_ReturnLines
ON T_Returns.ReturnID = T_ReturnLines.ReturnId
AND (T_Returns.ReturnDate BETWEEN '2008-08-23' AND '2008-08-30')
JOIN T_ReturnTypes
ON T_Returns.ReturnTypeID = T_ReturnTypes.ReturnTypeID
SQL = Scarcely Qualifies as a Language
August 29, 2008 at 1:47 pm
Hello
I want to use query 2 and 3 join together to return an accurate result set
August 29, 2008 at 1:58 pm
I'm pretty sure this will work:
[font="Courier New"]SELECT
SUM(OL.QuantityShipped) AS qty_shipped,
SUM(ISNULL(QuantityReturned, 0)) AS qty_returned
FROM
T_OrderLine OL LEFT OUTER JOIN
T_ReturnLines RL ON
OL.OrderLine = RL.OrderLine AND
RL.ReturnDate BETWEEN '2008-08-23' AND '2008-08-30'
WHERE
T_OrderLine.ShipDate BETWEEN '2008-08-23' AND '2008-08-30'
[/font]
You should also fix query 1 like Carl mentions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy