Syntax issue with CTE to Pivot function

Question

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;

score 1 · Answer 1 · answered Oct 16 '20 at 04:25

If the temporary table is causing an issue then one solution would be to create physical table(s). This could prevent needlessly repeating the code for each pivot. There are many ways to accomplish this, one way would be to create a 'report_datetimes' table to store the unique datetime ranges as well as a 'report_whoisactive' table to store the domain data. Then the SQL script could delete existing rows if necessary, insert new rows, and create the pivot tables. Something like this.

DDL

drop table if exists report_datetimes;
go
create table report_datetimes(
  r_id                      int identity(1,1) primary key not null,
  start_dtm                 datetime not null,
  end_dtm                   datetime not null);
go
create unique index ndx_unq_start_end_dtm on report_datetimes(start_dtm, end_dtm);
go

drop table if exists report_whoisactive;
go
create table report_whoisactive(
  w_id                      int identity(1,1) primary key not null,
  r_id                      int not null references report_datetimes(r_id),
  domain                    varchar(10) not null,
  login_name                varchar(256) not null,
  Total_Time                time,
  UserSecTot                time,
  UserCPUTot                time);
go
create unique index ndx_unq_r_domain_login on report_whoisactive(r_id, domain, login_name);
go

Script Part 1: Populate tables

declare
  @start_dtm    datetime=dateadd(hh,23,dateadd(dd,datediff(dd,0,getdate()-1),0)),
  @end_dtm      datetime=dateadd(hh,9,dateadd(dd,datediff(dd,0,getdate()-0),0)),
  @r_id         int;

/* get r_id if exists (and delete existing data), create if it doesn't */
select @r_id=r_id
from report_datetimes
where start_dtm=@start_dtm
      and end_dtm=@end_dtm;
if @@rowcount=0
    begin
        insert report_datetimes(start_dtm, end_dtm) values (@start_dtm, @end_dtm);
        select @r_id=cast(scope_identity() as int);
    end
else
    delete report_whoisactive where r_id=@r_id;

;WITH 
CTE AS (
    select (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) 
insert report_whoisactive(r_id, domain, login_name, Total_Time,UserSecTot, UserCPUTot)
select @r_id,
    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;

Script Part 2: Pivot tables which reference r_id

select * from
(select Domain, UserCPUTot from report_whoisactive where r_id=@r_id) as sourcetable
PIVOT(
    sum(UserCPUTot) 
    FOR Domain IN (
        [Region01], 
        [Region02] 

        )
) AS pivot_table;

select * from
(select Domain, UserSecTot from report_whoisactive where r_id=@r_id) as sourcetable
PIVOT(
    sum(UserSecTot) 
    FOR Domain IN (
        [Region01], 
        [Region02]
 
        )
) AS pivot_table;

've considered this and will probably go this route if can't solve the syntax question. I'd just create a stage table and truncate it each time rather than re-create. — user3525187, Oct 16 '20 at 13:51
Yea that makes sense and more closely matches the existing process. I wasn't sure if adding keys and accumulating the data was necessary so I was just being cautious. Regarding syntax it's not possible to re-use CTE's to create separate pivots afaik. Please let me know if you'd like this answer updated — SteveC, Oct 16 '20 at 14:33
Actually - I'm deleting my answer (as it doesn't fix your fundamental problem). This one is a better solution. PS. Fun fact... you cannot downvote your own answer! — seanb, Oct 16 '20 at 14:43

score 1 · Accepted Answer · answered Oct 21 '20 at 06:11

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;

Outstanding! Now to study how I was going wrong. Is it really just as simple as making that last select with CASE a cte? And is that because cteoutput hasn't been called and therefore valid in PIVOT? — user3525187, Oct 24 '20 at 03:39

Syntax issue with CTE to Pivot function

2 Answers2