December 14, 2015 at 6:05 am
I tried below combining it into three CTE but still no luck 🙁
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_Hz*count(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
December 14, 2015 at 7:21 pm
I expanded the code a bit and added more rows to list for month and year as two separate columns
CREATE TABLE #Temp (When_Added_To_Sec_Group DATE , UserID INT)
INSERT #Temp Values ('10/2/2015',1), ('10/1/2015',2), ('11/24/2015',3),
('11/20/2015',4), ('11/18/2015',5), ('12/9/2015',6), ('1/16/2016',7), ('1/27/2016',8),('2/2/2016',9);
WITH Cnt AS(
SELECT COUNT(DISTINCT t.UserID) AS Value, MONTH(t.When_Added_To_Sec_Group) As Mnth, Year(t.When_Added_To_Sec_Group) As yr
FROM #Temp t
GROUP BY MONTH(t.When_Added_To_Sec_Group), Year(t.When_Added_To_Sec_Group)
)
SELECT t1.yr,t1.mnth, sum(t2.value) as runningSum_perYear
FROM cnt as t1 inner join cnt as t2 on (t2.mnth <=t1.mnth and t2.yr<=t1.yr)
Group by t1.mnth, t1.yr
----------------------------------------------------
December 14, 2015 at 7:23 pm
danny.delgado 65386 (12/14/2015)
I tried below combining it into three CTE but still no luck 🙁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_Hz*count(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
When you say no luck, what exactly are you encountering. I understood the DATEFROMPARTS function is not in 2008 version. Is this what you mean?
----------------------------------------------------
December 14, 2015 at 7:30 pm
I got lost here
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
To me this makes no sense.... But i'm not that bright so maybe some one can explain this to me. It appears you have nested?? queries inside the select of your final select query? I don't see how your joining these together. for one result set. it looks like a mis-match of result sets.
***SQL born on date Spring 2013:-)
December 14, 2015 at 7:48 pm
Hi Thomas,
I think my syntax/logic in not correct that is why it's confusing...Apologies as I am not that expert on CTE's so hoping for your kind help...
The query below is to get the Peak number of users from Concurrent CTE...
Select Peak_concurrent_users, concurrent_date from Concurrent
The query below is to SUM the total_Host capacity which I am hoping to query from Capacity CTE...
select SUM(Host_Capacity), Host_Boot_Time from Capacity
where When_Added_To_Group=Concurrent_date, When_Added_To_Group=Host_Boot_time
I guess my syntax/logic is wrong...Appreciate if you can help. Thanks in advance.
December 14, 2015 at 7:55 pm
Hi Danny,
It appears that you are mixing 3 queries with nothing in common at the bottom. You need some common element that they can join together on. If not they need to be 3 separate queries or datasets for your report. What are you trying to achieve as your end goal? Can you mock up a quick dummy result set of columns and rows?
***SQL born on date Spring 2013:-)
December 14, 2015 at 8:04 pm
MS SQL version is 2012 SP1....Below are some of the errors I encountered...
Incorrect Syntax near the keyword Select...
Incorrect Syntax near ','
I tried the below query and i got the error
Incorrect syntax near the keyword 'where'
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,
Peak_Users = (Select Peak_concurrent_users from Concurrent),
ConcurrentDate = (Select concurrent_date from Concurrent),
HostCapacity = (select SUM(Host_Capacity) from Capacity),
HostBootTime = (select Host_Boot_Time from Capacity),
where When_Added_To_Group=Concurrent_date, When_Added_To_Group=Host_Boot_time
from Cnt T1
Hope my description makes sense...Thanks.
December 14, 2015 at 8:22 pm
What are you actually trying to achieve ?
The query is only partly formed and will never work the way it is.
Your syntax errors are due to having commas where there should be no commas and incorrect order of statements.
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,
Peak_Users = (Select Peak_concurrent_users from Concurrent),
ConcurrentDate = (Select concurrent_date from Concurrent),
HostCapacity = (select SUM(Host_Capacity) from Capacity),
HostBootTime = (select Host_Boot_Time from Capacity), --this comma should not be here
from Cnt T1 --from MUST be before the where statement
where
When_Added_To_Group=Concurrent_date, --this comma needs to be AND or OR
When_Added_To_Group=Host_Boot_time
December 14, 2015 at 8:33 pm
I have no idea if this will even work. If your training this many CTE's together there probably is a better way.
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_Hz*count(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, SUM(t2.Entitled_Users) as Entitled_Users, Peak_concurrent_users ,SUM(Host_Capacity)
from Cnt T2
INNER JOIN Cnt t1 on T2.When_Added_To_Group <=T1.When_Added_To_Group
INNER JOIN Concurrent Crnt ON crnt.Concurrent_Date = t1.When_Added_To_Group
INNER JOIN Capacity Cp ON cp.Host_Boot_Time = t1.When_Added_To_Group
GROUP BY t1.When_Added_To_Group
***SQL born on date Spring 2013:-)
December 15, 2015 at 8:34 pm
Hi Thomas,
after the CTE's I tried the below query and it seems to be working...
Select cu.Hub, cu.Concurrent_Date, Entitled_Users, cu.Peak_Concurrent_Users, (Select SUM(t2.Entitled_Users) as Entitled_Users
from Cnt T2
where T2.When_Added_To_Group <=t1.When_Added_To_Group) as Entitlement_Running_Total
from Cnt T1
INNER JOIN Concurrent cu on cu.Concurrent_Date=t1.When_Added_To_Group
group by cu.Concurrent_Date, cu.Hub, Entitled_Users, cu.Peak_Concurrent_Users, t1.When_Added_To_Group
order by cu.Concurrent_Date desc
Below are few example of the output...
Hub|Concurrent_Date|Entitled_Users|Peak_Concurrent_Users|Entitlement_Running_Total
Hub1|1/12/2015 12:00 AM|207|112|526
Hub1|1/11/2015 12:00 AM|20|124|319
Hub1|1/10/2015 12:00 AM|7|112|299
Hub1|1/09/2015 12:00 AM|30|85|292
now I am trying to add the capacity CTE to the rest fo the CTE's but I cannot make the Capacity_Running_Total....
,Capacity AS (
select hs.hub as 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_Hz*count(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 cu.Hub, cu.Concurrent_Date, Entitled_Users, cu.Peak_Concurrent_Users, SUM(cap.Host_Capacity) as Hub_Capacity, (Select SUM(t2.Entitled_Users) as Entitled_Users
from Cnt T2
where T2.When_Added_To_Group <=t1.When_Added_To_Group) as Entitlement_Running_Total
from Cnt T1
INNER JOIN Concurrent cu on cu.Concurrent_Date=t1.When_Added_To_Group
INNER JOIN Capacity cap on cap.Hub=cu.Hub
group by cu.Concurrent_Date, cu.Hub, Entitled_Users, cu.Peak_Concurrent_Users, t1.When_Added_To_Group
order by cu.Concurrent_Date desc
And the output is something like this...You will notice that the value of Hub_Capacity is "always" 442 for each month...Hub_Capacity will depend if the HOSTS/Server is online/powered ON or was shutdown for Maintenance or if new HOST/Server is added to the existing capacity....The column hs.BOOT_TIME will show when the HOST/Server is powered ON/added to the capacity...hs.Boot_Time is almost similar to the When_Added_To_Group column...
Hub|Concurrent_Date|Entitled_Users|Peak_Concurrent_Users|Entitlement_Running_Total|Hub_Capacity
Hub1|1/12/2015 12:00 AM|207|112|526|442
Hub1|1/11/2015 12:00 AM|20|124|319|442
Hub1|1/10/2015 12:00 AM|7|112|299|442
Hub1|1/09/2015 12:00 AM|30|85|292|442
Since the HOSTS/Server is not powered ON/Not added every Month I used the Hub Column to join it to the rest of the CTE's...I am not sure whether I need to use hs.BOOT_TIME Column or Hub Column...
Below is the output I am hoping to get where the Hub_Capacity column will reflect the value when a new HOST is powered ON or added to the capacity or HOST is shutdown for maintenance...you will notice the value of the Hub_Capacity is not always 442....
Hub|Concurrent_Date|Entitled_Users|Peak_Concurrent_Users|Entitlement_Running_Total|Hub_Capacity
Hub1|1/12/2015 12:00 AM|207|112|526|442
Hub1|1/11/2015 12:00 AM|20|124|319|376
Hub1|1/10/2015 12:00 AM|7|112|299|348
Hub1|1/09/2015 12:00 AM|30|85|292|282
Thanks in advance 🙂
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply