Table Name Error

[renee35]

Could someone please explain to me why I am getting this error for the code below:

The column prefix 'p2' does not match with a table name or alias name used in the query.



update #finalnumbers
set
TempsThirteenWeekAvg = p2.TempsThirteenWeekAvg, BudgetTempsThirteenWeekAvg = p2.BudgetTempsThirteenWeekAvg,
ClientsThirteenWeekAvg = p2.ClientsThirteenWeekAvg, RegHrsThirteenWeekAvg = p2.RegHrsThirteenWeekAvg, OtherHrsThirteenWeekAvg = p2.OtherHrsThirteenWeekAvg,
AddsThirteenWeekAvg = p2.AddsThirteenWeekAvg, DropsThirteenWeekAvg = p2.DropsThirteenWeekAvg, TotalHrsThirteenWeekAvg = p2.TotalHrsThirteenWeekAvg,
AvgBillRateThirteenWeekAvg = p2.AvgBillRateThirteenWeekAvg, AvgPayRateThirteenWeekAvg = p2.AvgPayRateThirteenWeekAvg, MarkUpPctThirteenWeekAvg = p2.MarkUpPctThirteenWeekAvg,
FTEThirteenWeekAvg = p2.FTEThirteenWeekAvg, AvgRegHrsThirteenWeekAvg = p2.AvgRegHrsThirteenWeekAvg, AveOtherHrsThirteenWeekAvg = p2.AveOtherHrsThirteenWeekAvg,
AvgTotalHrsThirteenWeekAvg = p2.AvgTotalHrsThirteenWeekAvg, TimeCardNormalThirteenWeekAvg = p2.TimeCardNormalThirteenWeekAvg, TimeCardExceptionsThirteenWeekAvg = p2.TimeCardExceptionsThirteenWeekAvg,
TotalTimeCardsThirteenWeekAvg = p2.TotalTimeCardsThirteenWeekAvg
from #finalnumbers p
INNER JOIN
(
Select r.reportunitid,
TempsThirteenWeekAvg = AVG(ISNULL(p2.TempsonStreet,0.00)),
BudgetTempsThirteenWeekAvg = AVG(ISNULL(p2.BudgetTemps,0.00)),
ClientsThirteenWeekAvg = AVG(ISNULL(p2.Clients,0.00)),
RegHrsThirteenWeekAvg = AVG(ISNULL(p2.RegHrs,0.00)),
OtherHrsThirteenWeekAvg = AVG(ISNULL(p2.OtherHrs,0.00)),
AddsThirteenWeekAvg = AVG(ISNULL(p2.Adds,0.00)),
DropsThirteenWeekAvg = AVG(ISNULL(p2.Drops,0.00)),
TotalHrsThirteenWeekAvg = AVG(ISNULL(p2.TotalHrs,0)),
AvgBillRateThirteenWeekAvg = CASE WHEN sum(p2.TempsonStreet) > 0 THEN SUM(p2.TempsonStreet * p2.AvgBillRate)/SUM(p2.TempsonStreet) ELSE 0.00 END,
AvgPayRateThirteenWeekAvg = CASE WHEN sum(p2.TempsonStreet) > 0 THEN SUM(p2.TempsonStreet * p2.AvgPayRate)/SUM(p2.TempsonStreet) ELSE 0.00 END,
MarkUpPctThirteenWeekAvg = CASE WHEN SUM(p2.TempsonStreet * p2.AvgPayRate) <> 0 THEN SUM(p2.MarkupPct)/SUM(p2.TempsonStreet * p2.AvgPayRate) ELSE 0.00 END,
FTEThirteenWeekAvg = AVG(ISNULL(p2.fulltimeeqiv,0.00)),
AvgRegHrsThirteenWeekAvg = CASE WHEN sum(p2.TempsonStreet) > 0 THEN SUM(p2.RegHrs)/SUM(p2.TempsonStreet) ELSE 0.00 END,
AveOtherHrsThirteenWeekAvg = CASE WHEN sum(p2.TempsonStreet) > 0 THEN SUM(p2.OtherHrs)/SUM(p2.TempsonStreet) ELSE 0.00 END,
AvgTotalHrsThirteenWeekAvg = CASE WHEN sum(p2.TempsonStreet) > 0 THEN SUM(p2.TotalHrs)/SUM(p2.TempsonStreet) ELSE 0.00 END,
TimeCardNormalThirteenWeekAvg = Avg(ISNULL(p2.TimecardNormal,0.00)),
TimeCardExceptionsThirteenWeekAvg = Avg(ISNULL(p2.TimecardExceptions,0.00)),
TotalTimeCardsThirteenWeekAvg = Avg(ISNULL(p2.TotalTimeCards,0.00))
from #ReportUnitSummary p2
INNER JOIN ProfitCenterReportUnitMap r ON r.reportunitid = p2.reportunitid
JOIN FiscalWeekMap f ON f.WorkYear = p2.FiscalYear AND f.WorkPeriodNum = p2.WorkPeriodNum
and f.enddate between dateadd(w, -12, @currentweek) and @currentweek
Group by r.reportUnitId--, workperiodnum, fiscalyear

Thanks a bunch!!

-T

 

[renee35]

Nevermind- I got it!!

Thanks a bunch!!

-T

 

[dfwalton]

Can you post your solution. Its the same problem I have.
Thanks

 

[baran121]

you used p2 somewhere which does not match with a table name .