Syntax issue with CTE to Pivot function

I’m having a problem getting syntax correct from variables to CTE to Pivot. I’m capturing sp_whoisactive to a table and am now aggregating CPU and Duration by Region. I then want to pivot those results for easy charting in Excel. My workaround was to output to a #Temp table which causes issues in SSIS when attempting to output to sheets of Excel.

I pared down the CASE statement for brevity. There are approximately 30 regions.

How do I wrap this in CTE so I can pivot using start and end variable dates and eliminate #Temp tables?

IF OBJECT_ID('tempdb..#MySP') IS NOT NULL
DROP TABLE #MySP


DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = DATEADD(hh,23,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0))
SET @EndDate =  DATEADD(hh,9,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))

;WITH CTE AS
(
select --login_name, login_time, session_id,
(Substring([dd hh:mm:ss.mss],1,2) * 86400) + (Substring([dd hh:mm:ss.mss],4,2) * 3600) + (Substring([dd hh:mm:ss.mss],7,2) * 60) + (Substring([dd hh:mm:ss.mss],10,2))  as TotalSec
  ,t.*
from (
    select w.*,
        row_number() over(partition by session_id, login_time, login_name order by collection_time desc) rn
    from Regional.DBA.WhoIsActive w
) t 
where rn = 1 and login_time between  @StartDate and @EndDate 
), RESULT as
(
select  CTE.login_name  ,   SUM(CTE.TotalSec ) as UserSecTot, SUM(CONVERT(int,REPLACE(cte.CPU,',',''))) as UserCPUTot from CTE 
Group by CTE.login_name 
) 
select  @StartDate as StartDate, @EndDate as EndDate,
CASE
    WHEN login_name = 'sa' then 'sa'
    WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' and login_name like '%Region01%' or login_name like '%_V1%' THEN 'Region01'
    WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' and login_name like '%Region02%' or login_name like '%_V2%' THEN 'Region02'
    WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' then 'z' + ParseName(Replace(login_name,'','.'),1)
    WHEN ParseName(Replace(login_name,'','.'),2) like '%Reg01%' then 'Region01'
    WHEN ParseName(Replace(login_name,'','.'),2) like '%Reg02%' then 'Region02'

ELSE ParseName(Replace(login_name,'','.'),2) 
END as Domain , login_name,  DBA.udf_SecToHHMMSS(UserSecTot) as Total_Time, UserSecTot, UserCPUTot 
into #MySP
from RESULT

select * from #MySP 

select * from
(select Domain, UserCPUTot from #MySP) as sourcetable
PIVOT(
    sum(UserCPUTot) 
    FOR Domain IN (
        [Region01], 
        [Region02] 

        )
) AS pivot_table;

select * from
(select Domain, UserSecTot from #MySP) as sourcetable
PIVOT(
    sum(UserSecTot) 
    FOR Domain IN (
        [Region01], 
        [Region02]
 
        )
) AS pivot_table;

Answer

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = DATEADD(hh,23,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0))
SET @EndDate =  DATEADD(hh,9,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-0),0))

;WITH CTE AS
(
    select --login_name, login_time, session_id,
    (Substring([dd hh:mm:ss.mss],1,2) * 86400) + (Substring([dd hh:mm:ss.mss],4,2) * 3600) + (Substring([dd hh:mm:ss.mss],7,2) * 60) + (Substring([dd hh:mm:ss.mss],10,2))  as TotalSec
      ,t.*
            from (
                select w.*,
                    row_number() over(partition by session_id, login_time, login_name order by collection_time desc) rn
                from Regional.DBA.WhoIsActive w
            ) t 
    where rn = 1 and login_time between  @StartDate and @EndDate 
), RESULT as
(
    select  CTE.login_name  ,   SUM(CTE.TotalSec ) as UserSecTot, SUM(CONVERT(int,REPLACE(cte.CPU,',',''))) as UserCPUTot from CTE 
    Group by CTE.login_name 
) 

, cteoutput as (
select  @StartDate as StartDate, @EndDate as EndDate,
CASE
    WHEN login_name = 'sa' then 'sa'
    WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' and login_name like '%Region01%' or login_name like '%_V1%' THEN 'Region01'
    WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' and login_name like '%Region02%' or login_name like '%_V2%' THEN 'Region02'
    WHEN ParseName(Replace(login_name,'','.'),2) like '%vhamaster%' then 'z' + ParseName(Replace(login_name,'','.'),1)
    WHEN ParseName(Replace(login_name,'','.'),2) like '%Reg01%' then 'Region01'
    WHEN ParseName(Replace(login_name,'','.'),2) like '%Reg02%' then 'Region02'

ELSE ParseName(Replace(login_name,'','.'),2) 
END as Domain , login_name,  DBA.udf_SecToHHMMSS(UserSecTot) as Total_Time, UserSecTot, UserCPUTot 

from RESULT
)


select * from
(select Domain, UserCPUTot from cteoutput) as sourcetable
PIVOT(
    sum(UserCPUTot) 
        FOR Domain IN (
        [Region01], 
        [Region02] 

        )
) AS pivot_table;