How can I access this field of a temp table?

Question

I have a complex query that stuffs stuff into temp tables, then reads and combines those temp tables. At the end of this, I have this code:

    Select distinct CSDirector=r1.CSDirector,
        Category = r1.Category,
        Segment = r1.Segment,
        r1.unit,
        NumUnits=r1.NumUnits,
        NumUnitsLast=r2.NumUnits,
        MonthSales=r1.MonthSales,
        MonthSalesLast=r2.MonthSales,
        MonthPerc = (r1.MonthSales - r2.MonthSales) / case when isnull(r2.MonthSales,0) = 0 then 1 else r2.MonthSales end, 
        YTDSales = r1.YTDSales, 
        YTDSalesLast = r2.YTDSales,
        YTDPerc= (r1.YTDSales - r2.YTDSales) / case when isnull(r2.YTDSales,0) = 0 then 1 else r2.YTDSales end,
        ProjSales = r1.ProjSales, 
        YTDProjSales = r1.YTDProjSales,
        YTDBudgetPerc = r1.YTDBudgetPerc,
        NewBiz = isnull((Select NewBiz from MasterUnitsProjSales where Unit = r1.unit and Cyear=@Cyear),0)  
into    #CombinedYears
From #CurrentYear r1 inner join #PriorYear r2 on r1.unit=r2.unit
order by NewBiz,r1.Unit

declare @Unit varchar(30);
declare @paramdate datetime;
set @paramdate = convert(datetime,convert(char(4),@CYear)
                +right('0'+convert(varchar(2),@CMonth),2)
                +'01') 

IF OBJECT_ID('tempdb.dbo.#Units', 'U') IS NOT NULL
DROP TABLE #Units

select distinct unit
into #Units
from ReportingMonthlySales;

select 
    u.Unit
  , New      = sum(case when ccl.Subcategory = 'New'      then rms.MonthlySales else 0 end)
  , Assumed  = sum(case when ccl.Subcategory = 'Assumed'  then rms.MonthlySales else 0 end)
  , Existing = sum(case when ccl.Subcategory = 'Existing' then rms.MonthlySales else 0 end)
  , Organic  = sum(case when ccl.Subcategory = 'Organic'  then rms.MonthlySales else 0 end)
into    #CategorizedUnits
from #Units u
join CustomerCategoryLog   ccl on u.Unit = ccl.Unit and @paramdate >= ccl.begindate and @paramdate <= ccl.enddate
join ReportingMonthlySales rms on u.Unit = rms.Unit and rms.cyear  = @cyear and rms.cmonth = @cmonth
group by u.unit;

-- Now combine combined years and categorized units
select CY.CSDirector, CY.Category, CY.Segment, CY._Unit, 
       CU.New, CU.Assumed, CU.Existing, CU.Organic, 
       CY.NumUnits, CY.NumUnitsLast, CY.MonthSales, CY.MonthSalesLast, 
       CY.MonthPerc, CY.YTDSales, CY.YTDSalesLast, CY.YTDPerc,
       CY.ProjSales, CY.YTDProjSales, CY.YTDBudgetPerc, CY.NewBiz 
from #CombinedYears CY
left join #CategorizedUnits CU on CU.Unit = CY.Unit

...but it fails with "Error 207: Invalid column name 'r1'." on this line:

select CY.CSDirector, CY.Category, CY.Segment, CY.r1.unit, 

So, I thought perhaps I needed to change this:

Select distinct CSDirector=r1.CSDirector,
        Category = r1.Category,
        Segment = r1.Segment,
        r1.unit,

...to this:

Select distinct CSDirector=r1.CSDirector,
        Category = r1.Category,
        Segment = r1.Segment,
        _Unit = r1.unit,

...along with this:

select CY.CSDirector, CY.Category, CY.Segment, CY.r1.unit, 

...to this:

select CY.CSDirector, CY.Category, CY.Segment, CY._Unit, 

...but when I try that, I get, "Error 207: Invalid column name '_Unit'." on this line:

select CY.CSDirector, CY.Category, CY.Segment, CY._Unit, 

So how can I grab the "Unit" value from the #CombinedYears table?

UPDATE

This also fails:

--select CY.CSDirector, CY.Category, CY.Segment, CY.r1.unit, 
--select CY.CSDirector, CY.Category, CY.Segment, CY._Unit, 
select CY.CSDirector, CY.Category, CY.Segment, CY.unit,

...with "Error 207: Invalid column name 'unit'."

UPDATE 2

This works:

--CREATE PROCEDURE [dbo].[sp_ReportMonthlySalesEnhanced]
--  @CYear  int;
--  @CMonth int;
--as

declare @CYear  int = 2017; -- remove before above is uncommented
declare @CMonth int = 3; -- " "

IF OBJECT_ID('tempdb.dbo.#Units', 'U') IS NOT NULL
DROP TABLE #Units

IF OBJECT_ID('tempdb.dbo.#CurrentYear', 'U') IS NOT NULL
DROP TABLE #CurrentYear

IF OBJECT_ID('tempdb.dbo.#PriorYear', 'U') IS NOT NULL
DROP TABLE #PriorYear

IF OBJECT_ID('tempdb.dbo.#CombinedYears', 'U') IS NOT NULL
DROP TABLE #CombinedYears

IF OBJECT_ID('tempdb.dbo.#CategorizedUnits', 'U') IS NOT NULL
Drop Table #CategorizedUnits

Select  CSDirector,
        Category,
        Segment,
        r1.unit,
        NumUnits=isnull((Select sum(NumUnits) from ReportingMonthlySales where unit=r1.unit and 
cyear=r1.cyear and cmonth = @Cmonth),0),    
        MonthSales=isnull((Select sum(MonthlySales) from ReportingMonthlySales where unit=r1.unit and 
cyear=r1.cyear and cmonth = @Cmonth),0.00), 
        YTDSales = (Select sum(MonthlySales) From  ReportingMonthlySales where unit=r1.unit and 
cyear=r1.cyear and cmonth <= @Cmonth),
        ProjSales =  (Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and 
Cyear=r1.cyear),
        YTDProjSales = (Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and 
Cyear=r1.cyear) / 12 * @Cmonth,
        YTDBudgetPerc = (Select sum(MonthlySales) From  ReportingMonthlySales where unit=r1.unit and 
cyear=r1.cyear and cmonth <= @Cmonth) / 
            case when (Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and 
Cyear=r1.cyear) = 0 then 1 else ((Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and 
Cyear=r1.cyear) / 12 * @Cmonth) end
into    #CurrentYear
From    MasterUnitsProjSales r1 
where   r1.Cyear=@CYear
order by r1.NewBiz,r1.Unit

Select  CSDirector,
        Category,
        Segment,
        r1.unit,
        NumUnits=isnull((Select sum(NumUnits) from ReportingMonthlySales where unit=r1.unit and 
cyear=r1.cyear - 1 and cmonth = @Cmonth),0),
        MonthSales=isnull((Select sum(MonthlySales) from ReportingMonthlySales where unit=r1.unit and 
cyear=r1.cyear - 1 and cmonth = @Cmonth),0.00),
        YTDSales = (Select sum(MonthlySales) From  ReportingMonthlySales where unit=r1.unit and 
cyear=r1.cyear - 1 and cmonth <= @Cmonth),
        ProjSales =  (Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and 
Cyear=r1.cyear - 1 ),
        YTDProjSales = (Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and 
Cyear=r1.cyear - 1) / 12 * @Cmonth,
        YTDBudgetPerc = (Select sum(MonthlySales) From  ReportingMonthlySales where unit=r1.unit and 
cyear=r1.cyear - 1 and cmonth <= @Cmonth) / 
            case when (Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and 
Cyear=r1.cyear - 1 ) = 0 then 1 else ((Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and 
Cyear=r1.cyear - 1) / 12 * @CMonth) end
into    #PriorYear
From    MasterUnitsProjSales r1 
where r1.Cyear=@CYear
order by r1.NewBiz,r1.Unit

Select distinct CSDirector=r1.CSDirector,
        Category = r1.Category,
        Segment = r1.Segment,
        r1.unit [_Unit],  
        NumUnits=r1.NumUnits,
        NumUnitsLast=r2.NumUnits,
        MonthSales=r1.MonthSales,
        MonthSalesLast=r2.MonthSales,
        MonthPerc = (r1.MonthSales - r2.MonthSales) / case when isnull(r2.MonthSales,0) = 0 then 1 else r2.MonthSales end, 
        YTDSales = r1.YTDSales, 
        YTDSalesLast = r2.YTDSales,
        YTDPerc= (r1.YTDSales - r2.YTDSales) / case when isnull(r2.YTDSales,0) = 0 then 1 else r2.YTDSales end,
        ProjSales = r1.ProjSales, 
        YTDProjSales = r1.YTDProjSales,
        YTDBudgetPerc = r1.YTDBudgetPerc,
        NewBiz = isnull((Select NewBiz from MasterUnitsProjSales where Unit = r1.unit and Cyear=@Cyear),0)  
into    #CombinedYears
From #CurrentYear r1 inner join #PriorYear r2 on r1.unit=r2.unit
order by NewBiz,r1.Unit

declare @Unit varchar(30);
declare @paramdate datetime;
set @paramdate = convert(datetime,convert(char(4),@CYear)
                +right('0'+convert(varchar(2),@CMonth),2)
                +'01') 

select distinct unit
into #Units
from ReportingMonthlySales;

select 
    u.Unit
  , New      = sum(case when ccl.Subcategory = 'New'      then rms.MonthlySales else 0 end)
  , Assumed  = sum(case when ccl.Subcategory = 'Assumed'  then rms.MonthlySales else 0 end)
  , Existing = sum(case when ccl.Subcategory = 'Existing' then rms.MonthlySales else 0 end)
  , Organic  = sum(case when ccl.Subcategory = 'Organic'  then rms.MonthlySales else 0 end)
into    #CategorizedUnits
from #Units u
join CustomerCategoryLog   ccl on u.Unit = ccl.Unit and @paramdate >= ccl.begindate and @paramdate <= ccl.enddate
join ReportingMonthlySales rms on u.Unit = rms.Unit and rms.cyear  = @cyear and rms.cmonth = @cmonth
group by u.unit;

-- Now combine combined years and categorized units
select CY.CSDirector, CY.Category, CY.Segment, CY._Unit, 
       CU.New, CU.Assumed, CU.Existing, CU.Organic, 
       CY.NumUnits, CY.NumUnitsLast, CY.MonthSales, CY.MonthSalesLast, 
       CY.MonthPerc, CY.YTDSales, CY.YTDSalesLast, CY.YTDPerc,
       CY.ProjSales, CY.YTDProjSales, CY.YTDBudgetPerc, CY.NewBiz 
from #CombinedYears CY
left join #CategorizedUnits CU on CU.Unit = CY._Unit

Answer 1

for a start...

select * from #CombinedYears  

should be able to identify the field name or to explicitly name the field... [_Unit] on the query below.

  Select distinct CSDirector=r1.CSDirector,
        Category = r1.Category,
        Segment = r1.Segment,
        r1.unit [_Unit],   -- here
        NumUnits=r1.NumUnits,
        NumUnitsLast=r2.NumUnits,
        MonthSales=r1.MonthSales,
        MonthSalesLast=r2.MonthSales,
        MonthPerc = (r1.MonthSales - r2.MonthSales) / case when isnull(r2.MonthSales,0) = 0 then 1 else r2.MonthSales end, 
        YTDSales = r1.YTDSales, 
        YTDSalesLast = r2.YTDSales,
        YTDPerc= (r1.YTDSales - r2.YTDSales) / case when isnull(r2.YTDSales,0) = 0 then 1 else r2.YTDSales end,
        ProjSales = r1.ProjSales, 
        YTDProjSales = r1.YTDProjSales,
        YTDBudgetPerc = r1.YTDBudgetPerc,
        NewBiz = isnull((Select NewBiz from MasterUnitsProjSales where Unit = r1.unit and Cyear=@Cyear),0)  
into    #CombinedYears
From #CurrentYear r1 inner join #PriorYear r2 on r1.unit=r2.unit
order by NewBiz,r1.Unit