create view with declare in script. Procedure and Function doesn't work

Question

I cant create view with declare. i even tried to create procedure but than i found that i cant create view with procedure. and i think same will be for function. only thing i can think of is to avoid declare. How can I find the way around? i need to have view table (in order to be able to see them in power bi in the future, i cant see it from stored procedure in it)

declare @T as table (
    Name nvarchar(100)
    , OBJECTID varchar(50)
    , started_working datetime
    ,STOPFROM datetime
    ,STARTDATE datetime
    ,STOPDATE datetime
    ,MODIFIEDDATETIME datetime
    ,START_STOP int
    ,STARTDESCRIPTION nvarchar(300)
    ,STOPDESCRIPTION nvarchar(300)
    ,wattage nvarchar (50)
    ,purpose nvarchar(300)
    ,location nvarchar(300)
    ,finished_working datetime
    ,oldDiff int
)

insert into @T
select 
    NAME
    ,OBJECTID
    ,STOPTO
    ,STOPFROM
    ,STARTDATE
    ,STOPDATE
    ,MODIFIEDDATETIME
    ,START_STOP
    ,STARTDESCRIPTION
    ,STOPDESCRIPTION
    ,wattage
    ,purpose
    ,location
    ,next_stopfrom
    ,diff
    FROM [MicrosoftDynamicsAX].[dbo].[mroobjectengineworkinghours]

 ;with rcte as (
    select
        *, started_working2 = started_working
        , next_date = cast(dateadd(dd, 1, started_working) as date), 1 step
    from 
        @T
    union all
    select
        Name,OBJECTID, started_working,STOPFROM,STARTDATE,STOPDATE,MODIFIEDDATETIME,START_STOP,STARTDESCRIPTION
    ,STOPDESCRIPTION,wattage
    ,purpose
    ,location, finished_working,oldDiff
        , cast(next_date as datetime)
        , dateadd(dd, 1, next_date), step + 1
    from
        rcte
    where
        next_date < finished_working
)

select
    Name,OBJECTID, started_working,STOPFROM,STARTDATE,STOPDATE,MODIFIEDDATETIME,START_STOP,STARTDESCRIPTION
    ,STOPDESCRIPTION,wattage
    ,purpose
    ,location,oldDiff, started_working2, finished_working
    , right(replace(str(diff / 60), ' ', 0), 2) + ':' + right(replace(str(diff % 60), ' ', 0), 2) hours_worked
from (
    select
        Name,OBJECTID, started_working,STOPFROM,STARTDATE,STOPDATE,MODIFIEDDATETIME,START_STOP,STARTDESCRIPTION
    ,STOPDESCRIPTION,wattage
    ,purpose
    ,location,oldDiff
        , case 
            when step = 1 then started_working 
            else started_working2 
        end started_working2
        , case 
            when step = max(step) over (partition by Name, started_working) 
            then finished_working else next_date 
        end finished_working
    from
        rcte
) t
cross apply (select datediff(mi, started_working2, finished_working) diff) ca
OPTION (MAXRECURSION 0);

Answer 1

I agree with Sean Lange.

Why not in your cte simply call the table directly?

Such as:

;with rcte as ( 
select *
     , started_working2 = started_working 
     , next_date = cast(dateadd(dd, 1, started_working) as date)
     , 1 step 
  from [MicrosoftDynamicsAX].[dbo].[mroobjectengineworkinghours] ...