I am having issue with a query I am trying to fix, which was not developed by me.
Basically what happens is that the join statement only seems to join to 1 part of the condition but not the other. Perhaps the query needs to be modified. In any event, I need to bring back both conditions in the query.
SELECT * INTO #tempHistory FROM dbo.Sales_History WHERE DATEDIFF(D,DateOfTransaction,@DateRequired) <= 14 AND DATEDIFF(D,DateOfTransaction,@DateRequired) >= 1 AND StoreID = @StoreID --the stored procedure always expects MondaySales as the first day of the week to be passed in as date required --First Load all the data from the database into a temp table SELECT @StoreID StoreID, @DateRequired DateRequired, c.CategoryID CategoryID, c.Category, sp.Name, sp.Id SalesProdID, sp.TASKUCode, sp.EISKUCode, ISNULL(sp.IncludeInBuildDef,0) IncludeInBuildDef, ISNULL(sp.IncludeInBuildExport,0) IncludeInBuildExport, 0 MondaySales, 0 MondayOC, 0 MondayMods, 0 TuesdaySales, 0 TuesdayOC, 0 TuesdayMods, 0 WednesdaySales, 0 WednesdayOC, 0 WednesdayMods, 0 ThursdaySales, 0 ThursdayOC, 0 ThursdayMods, 0 FridaySales, 0 FridayOC, 0 FridayMods, 0 SaturdaySales, 0 SaturdayOC, 0 SaturdayMods, 0 SundaySales, 0 SundayOC, 0 SundayMods, 0 TotalSales, 0 TotalOC, 0 TotalMods --INTO #tempBuild_SalesProductData FROM dbo.Prod_Menu m INNER JOIN dbo.Prod_MenuProducts mp ON m.Id = mp.MenuID INNER JOIN dbo.Prod_SalesProducts sp on mp.ProductId = sp.Id INNER JOIN dbo.Prod_ProductionCategories c on c.CategoryID = COALESCE(dbo.prod_GetProductionCategory(sp.CategoryID), sp.CategoryID) WHERE DATEDIFF(D, m.StartDate, GETDATE()) >= 0 AND DATEDIFF(D, m.EndDate, GETDATE()) < 0 AND ISNULL(sp.Deleted,0) = 0 ORDER BY sp.Name --Then update this temp table with data for the different days of the week --GET MondaySales DECLARE @DateRequired DateTime SET @DateRequired = '2015-7-06' DECLARE @StoreID Uniqueidentifier SET @StoreID = 'E54E7A48-89F4-4FC5-A373-14FEC18F5BA9' --UPDATE b SET MondaySales = p.Quantity select * FROM #tempBuild_SalesProductData b INNER JOIN ( SELECT SKUCode, ISNULL(CEILING(AVG(Quantity)),0) Quantity FROM ( SELECT ss.SKUCode, SUM(Quantity) Quantity FROM #tempHistory ss WHERE DATEDIFF(D, DateOfTransaction, @DateRequired) = 14 AND (ss.StoreID = @StoreID) AND ISNULL(ss.StaffFood, 0) = 0 AND ISNULL(ss.Waste, 0) = 0 GROUP BY SKUCode UNION SELECT ss.SKUCode, SUM(Quantity) Quantity FROM #tempHistory ss WHERE DATEDIFF(D, DateOfTransaction, @DateRequired) = 7 AND (ss.StoreID = @StoreID) AND ISNULL(ss.StaffFood, 0) = 0 AND ISNULL(ss.Waste, 0) = 0 GROUP BY SKUCode ) p GROUP BY SKUCode ) p ON (p.SKUCode = b.TASKUCode OR p.SKUCode = b.EISKUCode) WHERE StoreID = @StoreID AND b.DateRequired = @DateRequired select * from #tempBuild_SalesProductData where name='Sweet Potato Falafel & Hummus Wrap'
so, the query here has been altered slightly for debugging purposes, providing in date/store ID.
I believe the problem is in the join below:
p ON (p.SKUCode = b.TASKUCode OR p.SKUCode = b.EISKUCode)
It seems to only pull in the TASKUCode join and not the EISKUCode
how should the query been fixed to bring in data from both parts of the join?