July 7, 2008 at 11:16 am
Hello
I am using the following code to retrieve records, from an order table without duplicating the stock level, to provide an accurate stock level for analysis.:
USE Reporting_TEST_Restore
SELECT T_OrderLine.StockID, T_OrderLine.Quantity AS Qty, T_Items.Description,
T_Items.CatalogueID AS Cat_ID, T_OrderLine.QuantityShipped AS [QtyShipped],
T_OrderLine.LineStatus AS Line_Status, T_OrderHeader.OrderID AS [Order ID],
CONVERT(varchar(11), T_OrderHeader.OrderDate, 106) AS [Date of Order],
T_OrderHeader.OrderStatusID AS Order_Status_ID,
T_OrderLine.BackOrdFlag AS [BO Flag], [T_Items].[AvailableLev] AS [Stock Level]
FROM (Reporting_TEST_Restore.dbo.T_OrderHeader [T_OrderHeader]
INNER JOIN Reporting_TEST_Restore.dbo.T_OrderLine [T_OrderLine]
ON [T_OrderHeader].[OrderID]=[T_OrderLine].[OrderID]) INNER JOIN [Reporting_TEST_Restore].[dbo].[T_Items] [T_Items]
ON [T_OrderLine].[StockID]=[T_Items].[StockID]
WHERE [T_OrderLine].[LineStatus]=N'W' AND [T_OrderHeader].[OrderDate]<{ts '2008-07-03 00:00:00'}
AND [T_OrderHeader].[OrderStatusID]=N'W'
ORDER BY T_OrderHeader.OrderID
DESC, [T_Items].[CatalogueID], [T_OrderLine].[StockID]
----
Anytime i run the script i get duplicate entries of the same stock level, for an order that received a discount, thereby giving my end users inaccurate information about the stock level:
DISCOUNT DISCOUNT 1 0 W W True -346
DISCOUNT DISCOUNT 1 0 W W True -346
Can anyone help?
July 25, 2008 at 10:44 am
Obviously, the relationships between the objects involved in the query are not as simple as you might have thought.
However, you do have several options:
1) Add restrictions to the JOIN conditions and/or to the WHERE clause to further restrict the number of rows returned. Look at your data and the database schema in order to find missing or erroneous relationships that you should correct.
2) Add a GROUP BY clause to the query to restrict the number of rows in the final result set.
3) Use the DISTINCT declaration in the SELECT clause in order to only return distinct rows.
If you want a more accurate answer you'll have to post table DDL, sample data and expected results.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
July 25, 2008 at 10:49 am
I agree with the advice above, but you might try simplying with a view or CTE for part of the query (either with or without the stock level). Sometimes breaking up some of the joins makes it easier to build a query that removes duplicates.
A GROUP by is probably needed to do this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply