MsSQL

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesMsSQL

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old December 14th, 2015, 07:45 PM
danao danao is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Dec 2015
Posts: 1 danao User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 m 31 sec
Reputation Power: 0
How to Combine or Merge three CTE

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.Whe nAddedToGroup),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.Whe nAddedToGroup),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_Dat e),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_Dat e),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_TIM E),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.Whe nAddedToGroup),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.Whe nAddedToGroup),1)
)
, Concurrent
as (
SELECT c.EventType, DATEFROMPARTS(YEAR(c.Event_Date),MONTH(c.Event_Dat e),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_Dat e),1), c.Hub, c.EventType
)
, Capacity
as (
select hs.hub, hs.NAME, DATEFROMPARTS(YEAR(hs.BOOT_TIME),MONTH(hs.BOOT_TIM E),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


Thanks in advance.

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesMsSQL > How to Combine or Merge three CTE


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap