Create or Combine three CTE

SSAS and SSRS
,
1

I hope you can help/guide me, i have one working CTE and i would like to add two more SQL queries to have one SQL statement and I am encountering various type of error as I try to play around with the parameters...

I have less experience in CTE so please bear with me...my purpose is to create an SSRS report using report builder and I want to combine the output of each query into a column bar...

Here is the working CTE which is my first Column Bar in my SSRS report...

WITH Cnt AS (
select Count(Distinct UserID) as Entitled_Users, DATEFROMPARTS(YEAR(t.WhenAddedToGroup),MONTH(t.WhenAddedToGroup),1) as When_Added_To_Group
from Membership t
where FirstName not like '%test%' and LastName not like '%test%' and FirstName not like '%user%' and LastName not like '%user%' and Account_Disabled not like 'YES' and Obj_Type not like 'NON_USER' and Region not like 'EMEA' and Region not like 'CCLA' and SecGroup IN ('SecurityGroup1', 'SecurityGroup2', 'SecurityGroup3', 'SecurityGroup4')and curr_member like 'yes' and ATTUID is not null and WhenAddedToGroup is not null
Group By DATEFROMPARTS(YEAR(t.WhenAddedToGroup),MONTH(t.WhenAddedToGroup),1)
)
Select When_Added_To_Group, Entitled_Users, (Select SUM(t2.Entitled_Users) as Entitled_Users
from Cnt T2
where T2.When_Added_To_Group <=T1.When_Added_To_Group) as Running_Total
from Cnt T1

Below is the second SQL query that I am hoping I can merge or join with the above CTE which will be the second column bar of my Column Report in SSRS..

SELECT c.EventType, DATEFROMPARTS(YEAR(c.Event_Date),MONTH(c.Event_Date),1) as Concurrent_Date, MAX(c.MAX_Concurrent_Users) as Peak_Concurrent_Users, c.Hub
FROM vNon_Concurrent_Users c
where c.EventType like 'Broker_Daily_Max_Users' and c.Hub like 'TOK Hub'
group by DATEFROMPARTS(YEAR(c.Event_Date),MONTH(c.Event_Date),1), c.Hub, c.EventType

below is my third SQL Query which will be the third column bar of my SSRS report...

select hs.hub, hs.NAME, DATEFROMPARTS(YEAR(hs.BOOT_TIME),MONTH(hs.BOOT_TIME),1) as Host_Boot_Time,
ROUND(CAST(hs.CPU_CORE_COUNT as FLOAT)hs.CPU_Hzcount(cast(hs.HOSTID as BIGINT))/800000000,0) as Host_Capacity
from HVD_VPXV_HOSTS as hs WITH (NOLOCK,NOWAIT)
where hs.hub like 'TOK Hub'
group by hs.hub, hs.CPU_CORE_COUNT, hs.CPU_Hz, hs.NAME, hs.BOOT_TIME
order by hs.BOOT_TIME desc

the where clause of the three queries are:

When_Added_To_Group=Concurrent_date, When_Added_To_Group=Host_Boot_time

as mentioned above I tried playing around with the paramters but i get different errors...below is one (of many) statement which i tried that i can give as an example...

WITH Cnt AS (
select Count(Distinct UserID) as Entitled_Users, DATEFROMPARTS(YEAR(t.WhenAddedToGroup),MONTH(t.WhenAddedToGroup),1) as When_Added_To_Group
from HVDMembership t
where FirstName not like '%test%' and LastName not like '%test%' and FirstName not like '%user%' and LastName not like '%user%' and Account_Disabled not like 'YES' and Obj_Type not like 'NON_USER' and Region not like 'EMEA' and Region not like 'CCLA' and SecGroup IN ('SecurityGroup1', 'SecurityGroup2', 'SecurityGroup3', 'SecurityGroup4')and curr_member like 'yes' and ATTUID is not null and WhenAddedToGroup is not null
Group By DATEFROMPARTS(YEAR(t.WhenAddedToGroup),MONTH(t.WhenAddedToGroup),1)
)
, Concurrent
as (
SELECT c.EventType, DATEFROMPARTS(YEAR(c.Event_Date),MONTH(c.Event_Date),1) as Concurrent_Date, MAX(c.MAX_Concurrent_Users) as Peak_Concurrent_Users, c.Hub
FROM vNon_Concurrent_Users c
where c.EventType like 'Broker_Daily_Max_Users' and c.Hub like 'TOK Hub'
group by DATEFROMPARTS(YEAR(c.Event_Date),MONTH(c.Event_Date),1), c.Hub, c.EventType
)
, Capacity
as (
select hs.hub, hs.NAME, DATEFROMPARTS(YEAR(hs.BOOT_TIME),MONTH(hs.BOOT_TIME),1) as Host_Boot_Time,
ROUND(CAST(hs.CPU_CORE_COUNT as FLOAT)hs.CPU_Hzcount(cast(hs.HOSTID as BIGINT))/800000000,0) as Host_Capacity
from HVD_VPXV_HOSTS as hs WITH (NOLOCK,NOWAIT)
where hs.hub like 'TOK Hub'
group by hs.hub, hs.CPU_CORE_COUNT, hs.CPU_Hz, hs.NAME, hs.BOOT_TIME
)
Select When_Added_To_Group, Entitled_Users, (Select SUM(t2.Entitled_Users) as Entitled_Users
from Cnt T2
where T2.When_Added_To_Group <=T1.When_Added_To_Group) as Running_Total,
Select Peak_concurrent_users, concurrent_date from Concurrent,
select SUM(Host_Capacity), Host_Boot_Time from Capacity
where When_Added_To_Group=Concurrent_date, When_Added_To_Group=Host_Boot_time
from Cnt T1

2

You can only have one "final" query per set of CTEs. So you can wrrite:

with cte1 as (....), 
        cte2 as (....)
select col1 from cte1
join cte2 ...

but not

with cte1 as (....), 
        cte2 as (....)
select col1 from cte1
select col2 from cte2