Help With Invalid Column Name Error

[renee35]

I have the below code:

--build our data for each profit center:
INSERT INTO #profitCenterSummary(profitCenterId,workperiodnum, fiscalyear,
tempsonstreet, clients, totalhrs, reghrs,otherhrs,avgpayrate,avgbillrate,
fulltimeeqiv, avgtothrs, avgreghrs, aveotherhrs, markuppct, timecardnormal,timecardexceptions, totaltimecards, clientname, clientid)
(
select p.profitcenterid, r.workperiodnum, r.fiscalyear,
tempsonstreet, clients, totalhrs, reghrs,otherhrs,avgpayrate,avgbillrate,
fulltimeeqiv, avgtothrs, avgreghrs, aveotherhrs, markuppct, timecardnormal, timecardexceptions, totaltimecards, r.clientname, r.clientid
from profitcenter p
LEFT JOIN
(
select
m.parentprofitcenterid,
workperiodnum,
fiscalyear,
clientname, o.clientid,
'TempsonStreet' = count (distinct o.candidateid),
'Clients' = count (distinct o.clientid),
'TotalHrs' = sum (isnull(o.totalhours,0)),
'RegHrs' = sum(isnull(o.reghours,0)),
'OtherHrs' = sum(isnull(o.otherhours,0)),
'AvgPayRate' = Case when sum(isnull(o.pay,0)) = 0 then 0
when sum (isnull(o.totalhours,0)) = 0 then 0 else sum(isnull(o.pay,0))/sum (isnull(o.totalhours,0)) end,
'AvgBillRate' = Case when sum(isnull(o.billing,0)) = 0 then 0
when sum (isnull(o.totalhours,0)) = 0 then 0 else sum(isnull(o.billing,0))/sum (isnull(o.totalhours,0)) end,
'FullTimeEqiv' = sum(o.totalhours)/35,
'AvgTotHrs' = Case when sum(isnull(o.totalhours,0)) = 0 then 0 else sum(o.totalhours)/count (distinct o.candidateid) end,
'AvgRegHrs' = Case when sum(isnull(o.reghours,0)) = 0 then 0 else sum(o.reghours)/count (distinct o.candidateid) end,
'AveOtherHrs' = Case when sum(isnull(o.otherhours,0)) = 0 then 0 else sum(o.otherhours)/count (distinct o.candidateid) end,
'MarkupPct' = case when sum(isnull(o.pay,0)) <> 0 then sum((isnull(o.billing,0) - isnull(o.pay,0)))/sum(isnull(o.pay,0))*100 else 0 end,
'TimeCardNormal' = SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)<7) then 1 else 0 end),
'TimeCardExceptions' = SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)>7) then 1 else 0 end),
'TotalTimeCards' = SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)<7) then 1 else 0 end)+
SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)>7) then 1 else 0 end)
from omsdetail o
INNER JOIN profitcentertoprofitcentermap m ON m.profitcenterid = o.profitcenterid
INNER JOIN clients c on c.clientid = o.clientid
--where m.parentprofitcenterid = @profitcenterid --and weekending = @enddate
Group by c.clientname, c.clientid, m.parentprofitcenterid, workperiodnum, fiscalyear
)r ON r.parentprofitcenterid = p.profitcenterid and r.clientid = p.clientid

It works fine until I add the last join:
r.clientid = p.clientid

That is when I get the error: Invalid column name 'clientid'.

Please advise on what is wrong.

Thanks!

Thanks a bunch!!

-T

 

[renee35]

Help no longer needed. Thanks!

Thanks a bunch!!

-T