The stored procedure gives me what I want, which is a ranking with points for each store in the company based on certain fields, but I also want to compare this week's vs. the same week last year transactions. I thought I could just do another select command, but it's not working. The same I did use, worked fine on it's own - but when I insert it into the already existing code, it dies... any suggestions would be greatly appreciated.
Signed - newbie
=====SAMPLE STORED PROCEDURE====
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,
This is where I run into problems. If I delete this section, the stored procedure works find, but I would really like it to work.
<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 />
And this is the continued part of the good procedure.
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
===END SAMPLE STORED PROCEDUE===
-rngd
|