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