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?
thank you.