I have been pulling my hair out over this one.
What we are trying to do is pull sum values for the same item out of three different tables in the same DB (MSSQL '05) – Should be quite straight forward however...
The result should look a bit like below;
item code | initial population value | ordered value | sold value | cost price
The below query is pulling everything correctly except ordered value.
I have a suspicion the below snippet of the statement is to blame for this – the value that return from this below snippet are inflated by many multiples.
LEFT OUTER JOIN Sys2OrderItems ON Sys2OrderItems.bc_code = BaseListing.Code AND Sys2OrderItems.status = 2 INNER JOIN Sys2Order ON Sys2OrderItems.order_number = Sys2Order.order_number AND Sys2Order.order_location = @LocationNumber AND Sys2Order.order_approval_status = 3 AND Sys2Order.order_status = 3 AND Sys2Order.order_date > @InternalDate
I believe what may be happening is if the Order (Sys2Order) has multiple items (Sys2OrderItems) it is calculating the SUM value by the amount of items in the order to due to the number of rows in Sys2OrderItems for that ordernumber.
I hope this makes sense however been on this all day and may be totally wrong.
Any help greatly appreciated.
Full query code below;
SELECT BaseListing.Code, InitialValues.value AS InitialPopulation, SUM(Sys2InvoiceItems.Quantity) AS Sold, SUM(Sys2OrderItems.qty) AS recv, Sys1Item.Description, ItemGroupsParent.Name AS PrimaryGroup, ItemGroupsChild.Name AS ChildGroup, Sys2Item.Cost_Group1, Sys2Item.Cost_Group2, SUM(Sys2OrderItems.qty) AS Expr1 FROM BaseListing LEFT OUTER JOIN InitialValues ON BaseListing.Code = InitialValues.code AND InitialValues.location = @LocatioNumber LEFT OUTER JOIN Sys1Item ON Sys1Item.ItemNo = BaseListing.Code AND Sys1Item.ItemTypeID = 15 AND Sys1Item.Deleted = 0 LEFT OUTER JOIN Sys2InvoiceItems ON Sys2InvoiceItems.ItemID = Sys1Item.ItemID AND Sys2InvoiceItems.EntryDate > 733953 LEFT OUTER JOIN Sys2Invoices ON Sys2Invoices.InvoiceID = Sys2InvoiceItems.InvoiceID AND Sys2Invoices.EntryDate > 733953 LEFT OUTER JOIN Sys2Accounts ON Sys2Accounts.RoomID = Sys2Invoices.RoomID AND Sys2Accounts.Deleted = 0 AND Sys2Accounts.Inactive = 0 LEFT OUTER JOIN Sys2OrderItems ON Sys2OrderItems.bc_code = BaseListing.Code AND Sys2OrderItems.status = 2 INNER JOIN Sys2Order ON Sys2OrderItems.order_number = Sys2Order.order_number AND Sys2Order.order_location = @LocationNumber AND Sys2Order.order_approval_status = 3 AND Sys2Order.order_status = 3 AND Sys2Order.order_date > @InternalDate LEFT OUTER JOIN Sys2Item ON Sys2Item.Code = BaseListing.Code LEFT OUTER JOIN ItemGroupsParent ON ItemGroupsParent.ID = Sys2Item.Primary_ID LEFT OUTER JOIN ItemGroupsChild ON ItemGroupsChild.ID = Sys2Item.Sub_ID WHERE (BaseListing.IsPhysical = 0) GROUP BY BaseListing.Code, InitialValues.value, Sys1Item.Description, ItemGroupsParent.Name, ItemGroupsChild.Name, Sys2Item.Cost_Group1, Sys2Item.Cost_Group2