This is my join this is where the issue is i know this to be true
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 >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 >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 < --- 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
this is the output
What i want is
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
