Database

UPDATE BCQOH <br />
LEFT JOIN BCQOH_w_Cost ON BCQOH.[2ndItemNumber] = BCQOH_w_Cost.[2ndItemNumber] <br />
SET BCQOH.Cost = [BCQOH_w_Cost]![Cost];

SELECT TransID, StoreNo, StoreName, TotalPoints, TotalTransactions,
		RANK() OVER (ORDER BY TotalPoints DESC) AS Ranking
		FROM (
		-- SubQuery Starts here
		select WeeklyReports.TransID, WeeklyReports.StoreNo, Stores.StoreName, 
			-- Calculate TotalPoints first
			rank() over (order by abs(WeeklyReports.ActualVsTheoFoodCost) desc)+
			rank() over (order by abs(WeeklyReports.Cash) desc)+
			rank() over (order by WeeklyReports.SalesVsBudgetPercent)+
			rank() over (order by WeeklyReports.Overtime desc)+
			rank() over (order by WeeklyReports.Deletes desc)+
			rank() over (order by WeeklyReports.Puw112 desc)+
			rank() over (order by WeeklyReports.Puw58 desc)+
			rank() over (order by WeeklyReports.PuwDay desc)+
			rank() over (order by ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2) desc)+
			rank() over (order by ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2) desc) as TotalPoints,
			WeeklyReports.TotalTransactions,

<br />
--SELECT DISTINCT WeeklyReports.TransID, WeeklyReports.StoreNo, WeeklyReports.WeekEndDate, WeeklyReports.TotalTransactions, <br />
--	WeeklyReportsLY.WeekEndDate AS LastYearDate, WeeklyReportsLY.TotalTransactions AS LastYearTransactions, <br />
--	(WeeklyReports.TotalTransactions - WeeklyReportsLY.TotalTransactions) AS TransPM<br />
--FROM WeeklyReports INNER JOIN<br />
--	WeeklyReports AS WeeklyReportsLY ON DATEADD(week, - 52, WeeklyReports.WeekEndDate) = WeeklyReportsLY.WeekEndDate<br />
--WHERE (WeeklyReports.TotalTransactions is not null and WeeklyReports.TotalTransactions <> 0 and<br />
--	WeeklyReportsLY.TotalTransactions is not null and WeeklyReportsLY.TotalTransactions <> 0)<br />

        abs(WeeklyReports.ActualVsTheoFoodCost) as FoodCostVariance,
        rank() over (order by abs(WeeklyReports.ActualVsTheoFoodCost) desc) as ptsFoodCostVariance,
        abs(WeeklyReports.Cash) as Cash,
        rank() over (order by abs(WeeklyReports.Cash) desc) as ptsCash,
        WeeklyReports.SalesVsBudgetPercent as SalesVsBudgetPct,
        rank() over (order by WeeklyReports.SalesVsBudgetPercent) as ptsSalesVsBudget,
        WeeklyReports.Overtime,
        rank() over (order by WeeklyReports.Overtime desc) as ptsOT,
        WeeklyReports.Deletes,
        rank() over (order by WeeklyReports.Deletes desc) as ptsDeletes,
        WeeklyReports.Puw112,
        rank() over (order by WeeklyReports.Puw112 desc) as ptsPuw112,
        WeeklyReports.Puw58,
        rank() over (order by WeeklyReports.Puw58 desc) as ptsPuw58,
        WeeklyReports.PuwDay,
        rank() over (order by WeeklyReports.PuwDay desc) as ptsPuwDay,
        ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2) as VoidPct,
        rank() over (order by ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2) desc) as ptsVoidPct,
        ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2) as AllVoidPct,
        rank() over (order by ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2) desc) as ptsAllVoidPct,
        MAX(WeeklyReports.WeekEndDate) AS LastWeekEndDate, WeeklyReports.WeekEndDate
    from WeeklyReports
    inner join Stores on WeeklyReports.StoreNo = Stores.StoreNo
    group by WeeklyReports.WeekEndDate, Stores.StoreName, WeeklyReports.TransID,
        WeeklyReports.StoreNo, WeeklyReports.SalesVsBudgetPercent,
        ABS(WeeklyReports.ActualVsTheoFoodCost), abs(WeeklyReports.Cash),
        WeeklyReports.Overtime, WeeklyReports.Deletes,
        WeeklyReports.Puw112, WeeklyReports.Puw58, WeeklyReports.PuwDay,
        ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2),
        ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2),
        WeeklyReports.TotalTransactions
    having (WeeklyReports.WeekEndDate =
        (select MAX(WeekEndDate) as LastWeekEndingDate from WeeklyReports as WR2))
    ) as WeeklyStoreRanking
    -- SubQuery ends here
GROUP BY StoreNo, StoreName, TotalPoints, TransID, TotalTransactions
ORDER BY TotalPoints DESC