Broken Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Broken Query

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2001-12-03 : 18:47:08

OK-

This one has perplexed me all day. It is a little complex, but I'll try to break it down.

Here is one of the select statements I am running:

 
SELECT h.projectNumber, SUM(DISTINCT f.sTotal) As "shippingCost", SUM(DISTINCT h.hTotal) As "handlingCost", (SUM(DISTINCT f.sTotal)+SUM(DISTINCT h.hTotal)) As "totalCost" FROM


(SELECT projectNumber, SUM(x.theTotal) AS "sTotal"
FROM (SELECT tblOrders.orderID, customerID, tblBoxes.dateShipped, tblOrders.projectNumber, 
tblBoxes.shippingCost AS "theTotal" 
FROM tblBoxes INNER JOIN tblOrders ON tblBoxes.orderID=tblOrders.orderID) x
WHERE x.customerID=1 AND dateShipped>='11/01/2001' AND dateShipped<='11/30/2001'
GROUP BY x.orderID, x.customerID, x.projectNumber) f,

(SELECT t.projectNumber, SUM(t.Total) AS "hTotal"
FROM (SELECT tblOrders.projectNumber, tblOrders.customerID, tblBoxes.dateShipped, tblOrders.orderID, tblBoxes.boxID, tblBoxType.costPerBox, tblBoxes.numberItems,
CASE WHEN ((tblBoxes.numberItems * tblBoxType.costPerItem) + tblBoxType.costPerBox) > tblCustomers.maxBoxCharge 
THEN tblCustomers.maxBoxCharge
ELSE ((tblBoxes.numberItems * tblBoxType.costPerItem) + tblBoxType.costPerBox)
END AS "Total"
FROM tblOrders INNER JOIN tblBoxes ON tblOrders.orderID = tblBoxes.orderID
INNER JOIN tblBoxType ON tblBoxType.boxTypeID = tblBoxes.boxTypeID
INNER JOIN tblCustomers ON tblBoxType.customerID = tblCustomers.customerID) t
WHERE t.customerID=1 AND dateShipped>='11/01/2001' AND dateShipped<='11/30/2001'
GROUP BY t.orderID, t.projectNumber) h

WHERE f.projectNumber = h.projectNumber

GROUP BY  f.projectNumber, h.projectNumber

The results I get from that are:
Project Number Shipping Handling Total
00.9602.000 43.5400 35.2000 78.7400

The code above is basically a query that gets the shipping and handling results from two subqueries. Now if I run the subqueries seperately with the following code:


SELECT projectNumber, SUM(x.theTotal) AS "sTotal"
FROM (SELECT tblOrders.orderID, customerID, tblBoxes.dateShipped, tblOrders.projectNumber, 
tblBoxes.shippingCost AS "theTotal" 
FROM tblBoxes INNER JOIN tblOrders ON tblBoxes.orderID=tblOrders.orderID) x
WHERE x.customerID=1 AND dateShipped>='11/01/2001' AND dateShipped<='11/30/2001'
GROUP BY  projectNumber

Project Number Shipping
00.9602.000 48.6200


SELECT t.projectNumber, SUM(t.Total) AS "hTotal"
FROM (SELECT tblOrders.projectNumber, tblOrders.customerID, tblBoxes.dateShipped, tblOrders.orderID, tblBoxes.boxID, tblBoxType.costPerBox, tblBoxes.numberItems,
CASE WHEN ((tblBoxes.numberItems * tblBoxType.costPerItem) + tblBoxType.costPerBox) > tblCustomers.maxBoxCharge 
THEN tblCustomers.maxBoxCharge
ELSE ((tblBoxes.numberItems * tblBoxType.costPerItem) + tblBoxType.costPerBox)
END AS "Total"
FROM tblOrders INNER JOIN tblBoxes ON tblOrders.orderID = tblBoxes.orderID
INNER JOIN tblBoxType ON tblBoxType.boxTypeID = tblBoxes.boxTypeID
INNER JOIN tblCustomers ON tblBoxType.customerID = tblCustomers.customerID) t
WHERE t.customerID=1 AND dateShipped>='11/01/2001' AND dateShipped<='11/30/2001'
GROUP BY  t.projectNumber

Project Number Handling
00.9602.000 40.9500

As you can see the results are not the same. I'm assuming the problem is with the join at the bottom, but as far as I can tell it should work. Any ideas? Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-03 : 21:26:02

Haven't looked at this in detail but
SUM(DISTINCT f.sTotal) As "shippingCost

gives a danger sign.
If you have two values that happen to be the same even though they come from different records then it will only include one of them so you will be losing entries.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources