Data Not Displaying with Update Statement

[renee35]

Could someone please tell me why my data for the update statement is not displaying when I enter the below code:

create table #FinalNumbers (ProfitCenterId int, ReportUnitId int, DisplayOrder int, bottomlevelname varchar(255), midlevelname varchar(255), toplevelname varchar(255),
AddsWeek1 int)
insert into #FinalNumbers(DisplayOrder, ProfitCenterId, bottomlevelname, midlevelname, toplevelname,
AddsWeek1)
(
Select Distinct p2.ProfitCenterId, p2.DisplayOrder, pcDesc, r.ReportUnitDescription, r2.ReportUnitDescription,
--Select Distinct p2.DisplayOrder, pcDesc, r.ReportUnitDescription, r2.ReportUnitDescription, '',
SUM(CASE WHEN enddate = DATEADD(ww, -12, @currentWeek) then Adds else 0 end) as AddsWeek1
from #ProfitCenterSummary p
INNER JOIN profitcenter p2 ON p2.profitCenterId = p.profitcenterId
INNER JOIN profitcenterReportUnitMap pr ON pr.profitcenterid = p2.profitcenterid
INNER JOIN reportUnit r ON r.reportunitid = pr.reportunitid and r.reportLevel = 4
INNER JOIN ReportUnit r2 ON r.reportUnitParentId = r2.ReportUnitId
LEFT JOIN FiscalWeekMap m ON m.WorkYear = p.FiscalYear AND m.WorkPeriodNum = p.WorkPeriodNum
group by p2.ProfitCenterId, p2.DisplayOrder, pcDesc, r.ReportUnitDescription, r2.ReportUnitDescription
)

--get 13Week Averages
update #finalnumbers
set
TempsThirteenWeekAvg = p2.TempsThirteenWeekAvg, BudgetTempsThirteenWeekAvg = p2.BudgetTempsThirteenWeekAvg
from #finalnumbers p
INNER JOIN
(
Select m.parentprofitcenterid,
TempsThirteenWeekAvg = AVG(ISNULL(p2.TempsonStreet,0.00)),
BudgetTempsThirteenWeekAvg = AVG(ISNULL(p2.BudgetTemps,0.00))
from #ProfitCenterSummary p2
INNER JOIN profitcentertoprofitcentermap m ON m.profitcenterid = p2.profitcenterid
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 m.parentprofitcenterid
) p2 on p2.parentprofitcenterid = p.profitcenterid

When I do a select * from #finalnumbers the values for my update statement display "NULL" while the others display.

Thanks a bunch!!

-T

 

[ethorn10]

In your create table statement for #finalnumbers you never defined fields called TempsThirteenWeekAvg or BudgetTempsThirteenWeekAvg.