title: Previous and Current Week Quantities JOIN Issue
body: <p>This is my join this is where the issue is i know this to be true </p>
  
  <pre><code> SELECT DR.DivNo
          ,p.[ProductCode]
          ,p.ProductClass
          ,p.EmpNo
          ,[Description]
          ,[CGNo]
          ,[SCGNo]
          ,dr.Retail
          ,bd.[Buying Director]
    INTO #Product
    FROM .[PRODUCT] p
    LEFT JOIN [DIVRETAIL] DR ON p.ProductCode = DR.ProductCode
    LEFT JOIN vwAllBuyingDirectors bd ON p.EmpNo = bd.bd_BuyingDirector
    WHERE dr.ValidTo IS NULL
          AND dr.DivNo NOT LIKE '8__'
    GROUP BY DR.DivNo
          ,p.[ProductCode]
          ,p.ProductClass
          ,p.EmpNo
          ,bd.[Buying Director]
          ,[Description]
          ,[CGNo]
          ,[SCGNo]
          ,dr.Retail
  
          --select * from #Product
  
    SELECT  [Datetime]
          ,[FirstDayOfWeek]
          ,[FirstDayOfMonth]
          ,c.YearWeek
          ,s.NoOfStores
          ,s.DivNo
    INTO #stores
    FROM [Calendar] c
    LEFT JOIN Stores s ON c.FirstDayOfMonth = s.Validfrom
    WHERE c.[Datetime] = firstdayofweek
  
        --- GIVE STORE COUNT TO EACH DATETIME
    SELECT s.DivNo
          ,c.[Datetime]
          ,c.WeekofYear
          ,c.FirstdayofWeek
          ,c.YearWeek
          ,s.NoOfStores
    INTO #weeklystores
    FROM Calendar c
    LEFT JOIN #stores s ON c.FirstDayOfWeek = s.FirstDayOfWeek
    -- Join numbers from #stores according to first day of week.  Each week onmly has the first day of the week's store count as a reference.
  
  ------------------------------------------PREVIOUS WEEK---------------------    --------------------
   SELECT  [Datetime]
          ,[FirstDayOflastWeek]
          ,[FirstDayOflastMonth]
          ,c.YearWeek
          ,s.NoOfStores
          ,s.DivNo
    INTO #storesv2
    FROM [Calendar] c
    LEFT JOIN cbis799p.Portfolio.Stores s ON c.FirstDayOfMonth = s.Validfrom
    WHERE c.[Datetime] = FirstDayOfWeek
  
    --- GIVE STORE COUNT TO EACH DATETIME
    SELECT s.DivNo
          ,c.[Datetime]
          ,c.WeekofYear
          ,c.FirstdayoflastWeek
          ,c.YearWeek
          ,s.NoOfStores
    INTO #preweeklystores
    FROM Calendar c
    LEFT JOIN #storesv2 s ON c.FirstDayOfWeek = s.FirstDayOfLastWeek  
  
    /*
   ASSIGN STORE COUNTS AND WEEK NUMBERS TO POS DATA
  */
        CREATE TABLE #salescore (
          [DivNo] NVARCHAR(max)
          ,[ProductCode] INT
          ,[Description] NVARCHAR(max)
          ,[CGNo] NVARCHAR(max)
          ,[SCGNo] NVARCHAR(max)
          ,Retail DECIMAL(38, 2)
          ,[WeekOfYear] TINYINT
          ,FirstDayOfWeek DATETIME
          ,[Quantity] INT
          ,[Sales] DECIMAL(38, 2)
          ,[NoOfStores] INT
          ,[USW] DECIMAL(38, 0)
          )
  
    INSERT INTO #salescore
    SELECT pos.DivNo
          ,pos.ProductCode
          ,[Description]
          ,[CGNo]
          ,[SCGNo]
          ,p.Retail
          ,w.WeekOfYear
          ,w.FirstDayOfWeek
          ,Sum(Quantity) [Quantity]
          ,sum(pos.Retail) [Retail2]
          ,w.NoOfStores
          ,(Sum(Quantity) / w.NoOfStores) USW
    FROM .pos pos
    LEFT JOIN #weeklystores w ON pos.PosDate = w.[DATETIME]
    LEFT JOIN #Product p ON p.ProductCode = pos.ProductCode
          AND p.DivNo = pos.DivNo
          AND pos.DivNo = w.DivNo
    WHERE pos.DivNo NOT LIKE '8__' /* Get rid of IRL Regions */
    and w.YearWeek &gt;2013
          and w.YearWeek IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@YEAR, ',')
                )
          AND p.EmpNo IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@BD, ',')
                )
          AND p.CGNo IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@CGNo, ',')
                )
          AND p.SCGNo IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@SCGNo, ',')
                )
          AND p.ProductClass IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@ProductClass, ',')
                )
          AND p.ProductCode IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@ProductCode, ',')
                )
          AND pos.DivNo IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@Region, ',')
                )
          AND w.WeekOfYear IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@WOY, ',')
                )
          AND w.WeekOfYear IS NOT NULL /* Get rid of pos before goldthorp opening day */
    GROUP BY pos.DivNo
          ,pos.ProductCode
          ,w.WeekOfYear
          ,w.NoOfStores
          ,w.FirstDayOfWeek
          ,[Description]
          ,[CGNo]
          ,[SCGNo]
          ,p.Retail
          ---------------------------------------------
  
                CREATE TABLE #presales (
          [DivNo] NVARCHAR(max)
          ,[ProductCode] INT
          ,[Description] NVARCHAR(max)
          ,[CGNo] NVARCHAR(max)
          ,[SCGNo] NVARCHAR(max)
          ,Retail DECIMAL(38, 2)
          ,PrevWOY TINYINT
          ,FirstDayOfLastWeek DATETIME
          ,[PreQuantity] INT
          ,[Sales] DECIMAL(38, 2)
          ,[NoOfStores] INT
          ,[USW] DECIMAL(38, 0)
          )
  
    INSERT INTO #presales
    SELECT pos.DivNo
          ,pos.ProductCode
          ,[Description]
          ,[CGNo]
          ,[SCGNo]
          ,p.Retail
          ,w.WeekOfYear as PrevWOY
          ,w.FirstDayOfLastWeek
          ,SUM(Quantity) Quantity
          ,sum(pos.Retail) [Retail3]
          ,w.NoOfStores
          ,(Sum(Quantity) / w.NoOfStores) USW
    FROM pos pos
    LEFT JOIN #preweeklystores w ON pos.PosDate = w.[DATETIME]
    LEFT JOIN #Product p ON p.ProductCode = pos.ProductCode
          AND p.DivNo = pos.DivNo
          AND pos.DivNo = w.DivNo
    WHERE pos.DivNo NOT LIKE '8__' /* Get rid of IRL Regions */
    and w.YearWeek &gt;2013
          and w.YearWeek IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@YEAR, ',')
                )
          AND p.EmpNo IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@BD, ',')
                )
          AND p.CGNo IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@CGNo, ',')
                )
          AND p.SCGNo IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@SCGNo, ',')
                )
          AND p.ProductClass IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@ProductClass, ',')
                )
          AND p.ProductCode IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@ProductCode, ',')
                )
          AND pos.DivNo IN (
                SELECT Item
                FROM DataWarehouse.dbo.ufnSplit(@Region, ',')
                )
          --AND w.WeekOfYear IN (
          --    SELECT Item
          --    FROM DataWarehouse.dbo.ufnSplit(@WOY, ',')
          --    )
          AND w.WeekOfYear IS NOT NULL /* Get rid of pos before goldthorp opening day */
    GROUP BY pos.DivNo
          ,pos.ProductCode
          ,w.WeekOfYear
          ,w.NoOfStores
          ,w.FirstDayOfLastWeek
          ,[Description]
          ,[CGNo]
          ,[SCGNo]
          ,p.Retail
  
  
  
          CREATE TABLE #sales (
          [DivNo] NVARCHAR(max)
          ,[ProductCode] INT
          ,[Description] NVARCHAR(max)
          ,[CGNo] NVARCHAR(max)
          ,[SCGNo] NVARCHAR(max)
          ,Retail DECIMAL(38, 2)
          ,[WeekOfYear] TINYINT
          ,FirstDayOfLastWeek DATETIME
          ,[Quantity] int    
          ,[preQuantity] INT
          ,[Sales] DECIMAL(38, 2)
          ,[NoOfStores] INT
          ,[USW] DECIMAL(38, 0)
          ) 
          INSERT INTO #sales
          SELECT pos.DivNo
          ,pos.ProductCode
          ,p.Description
          ,p.CGNo
          ,p.SCGNo
          ,p.Retail
          ,pos.WeekOfYear
          ,ps.FirstDayOfLastWeek
          ,SUM(ps.PreQuantity)---- Quantity 
          ,Sum(pos.Quantity) [Quantity]---- PreQuantity
          ,sum(PS.Sales) Sales
          ,pos.NoOfStores
          ,(Sum(pos.Quantity) / pos.NoOfStores) USW
    FROM #salescore pos
         LEFT JOIN #presales PS 
           on pos.ProductCode = ps.ProductCode 
          and pos.DivNo= ps.DivNo 
          AND pos.FirstDayOfWeek = ps.FirstDayOfLastWeek &lt; --- Issue
  
  
    LEFT JOIN #Product p ON p.ProductCode = pos.ProductCode
          AND p.DivNo = pos.DivNo
    WHERE pos.DivNo NOT LIKE '8__' /* Get rid of IRL Regions */
    Group By
          pos.DivNo
          ,pos.ProductCode
          ,p.Description
          ,p.CGNo
          ,p.SCGNo
          ,p.Retail
          ,pos.WeekOfYear
          ,ps.FirstDayOfLastWeek
          ,pos.NoOfStores
  </code></pre>
  
  <p>this is the output</p>
  
  <p><a href="https://i.stack.imgur.com/SaD8D.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SaD8D.png" alt="enter image description here"></a></p>
  
  <p>What i want is</p>
  
  <p><a href="https://i.stack.imgur.com/cvJkW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cvJkW.png" alt="enter image description here"></a></p>
  
  <p>the issue is the first day of week against first day of last week, how can i resolve this basically i need the dates to match up Properly is that makes sence , its hard to explain the script is the same through out just certain data was put into certain tables so that everything could be gathered now i just need to alter my join so it all marry's up</p>