December 10, 2015 at 6:12 pm
Hi,
appreciate it very much if you can guide me, I have a table below where I want to calculate the Monthy trend on the total employees added to a domain security group.
When_Added_To_Sec_Group | UserID
|
2/10/2015 3:33:26 AM | User1
1/10/2015 12:10:30 PM | User2
24/11/2015 2:45:31 PM | User3
20/11/2015 12:35:42 PM | User4
18/11/2015 4:28:54 AM | User5
9/12/2015 1:16:27 AM | User6
I run SQL query hoping to get an output similar to the table below where the count of employees will be added for each month then add the monthly total on the succeeding month. For example:
October count is 2 employees and November count is 3 employees so the calculation should be 2 + 3 = 5 which 5 should appear on the row where November 2015 is.
Same with December where the previous month total is 5 plus 1 employee added for December so the calculation will be 2 + 3 + 1 = 6 which 6 should appear on the row where December 2015 is.
Monthly_Trend | UserID_Count
|
10-2015 | 2
11-2015 | 5
12-2015 | 6
I tried the SQL query below but it seems not showing the output that I wanted.
select Count(distinct UserID) AS UserID_Count, convert(varchar(6), WhenAddedToGroup, 112) as Monthly_Trend
from SecGroupMembership
group by convert(varchar(6), WhenAddedToGroup, 112)
the output for the above query is
Monthly_Trend | UserID_Count
|
201510 | 2
2015 11 | 3
201512 | 1
I hope my description makes sense. Thanks in advance for your help!
December 10, 2015 at 7:59 pm
I have been drinking a few beers. Here is my answer but I'd advise for one of the other gents on here to give you a higher performing one as I fear mine is not very efficient. :hehe: 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);
WITH Cnt AS(
SELECT COUNT(DISTINCT t.UserID) AS Value, MONTH(t.When_Added_To_Sec_Group) As Mnth
FROM #Temp t
GROUP BY MONTH(t.When_Added_To_Sec_Group)
)
SELECT Mnth, Value ,
(SELECT SUM(t2.Value) AS Value
FROM Cnt T2
WHERE T2.Mnth <= T1.Mnth) AS RunningTotal
FROM Cnt T1
DROP TABLE #Temp
***SQL born on date Spring 2013:-)
December 10, 2015 at 8:32 pm
Using the setup from thomas.
Be aware that you havent specifed what happens with year boundaries. So if you need to do it on a yearly basis also then its a minor change to this.
As thomas also said - this probably isnt all that efficient either 😀
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);
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 yr, Mnth, Value, sum(value) over (order by yr, mnth)
from cnt
DROP TABLE #Temp
December 10, 2015 at 11:35 pm
Hi,
thanks for your quick reply, when I tried the query on my actual table there discrepancy on the Running Total....for example I queried the Total distinct UserID by Count(Distinct UserID) and the result is 495...however on your query syntax it is great that every month it shows the running total however the overall total is equal to 525 which is higher than the suppose 495.
WITH Cnt AS(
select Count(Distinct t.UserID) AS Entitled_Users, MONTH(t.WhenAddedToGroup) As Mnth
from Membership] t
where FirstName not like '%test%' and LastName not like '%test%' 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 current_member_of_SecGroup like 'yes' and UserID is not null and WhenAddedToGroup is not null
group by MONTH(t.WhenAddedToGroup)
)
SELECT Mnth, Entitled_Users,
(SELECT SUM(t2.Entitled_Users) AS Entitled_Users
FROM Cnt T2
WHERE T2.Mnth <= T1.Mnth) AS RunningTotal
FROM Cnt T1
could it be that it also count the test account that was added to the security group even though I excluded them on the where clause?This query is for Asia Pacific Region and there are some accounts that are from outside Asia Pacific that are also members of SecurityGroup but I excluded them on the where clause as well...I am not sure if that affects the running total....
thanks again for your guidance/help.
December 11, 2015 at 6:56 am
This works as well and probably faster than my first post. I like the windows function approach as well. Probably many ways to skin this cat. I'm waiting for some one to show a Tally Table Method. It seems you can solve almost all problems with a Tally Table..LOL:-D
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);
WITH Cnt AS(
SELECT COUNT(DISTINCT t.UserID) AS Value, MONTH(t.When_Added_To_Sec_Group) As Mnth
FROM #Temp t
GROUP BY MONTH(t.When_Added_To_Sec_Group)
)
SELECT t1.Mnth, SUM(t2.Value)
FROM Cnt t1
INNER JOIN Cnt t2 ON T2.Mnth <= T1.Mnth
GROUP BY t1.Mnth
***SQL born on date Spring 2013:-)
December 11, 2015 at 6:59 am
danny.delgado 65386 (12/10/2015)
Hi,thanks for your quick reply, when I tried the query on my actual table there discrepancy on the Running Total....for example I queried the Total distinct UserID by Count(Distinct UserID) and the result is 495...however on your query syntax it is great that every month it shows the running total however the overall total is equal to 525 which is higher than the suppose 495.
WITH Cnt AS(
select Count(Distinct t.UserID) AS Entitled_Users, MONTH(t.WhenAddedToGroup) As Mnth
from Membership] t
where FirstName not like '%test%' and LastName not like '%test%' 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 current_member_of_SecGroup like 'yes' and UserID is not null and WhenAddedToGroup is not null
group by MONTH(t.WhenAddedToGroup)
)
SELECT Mnth, Entitled_Users,
(SELECT SUM(t2.Entitled_Users) AS Entitled_Users
FROM Cnt T2
WHERE T2.Mnth <= T1.Mnth) AS RunningTotal
FROM Cnt T1
could it be that it also count the test account that was added to the security group even though I excluded them on the where clause?This query is for Asia Pacific Region and there are some accounts that are from outside Asia Pacific that are also members of SecurityGroup but I excluded them on the where clause as well...I am not sure if that affects the running total....
thanks again for your guidance/help.
You get 495 from this? But 525 on the other query?
WITH Cnt AS(
select Count(Distinct t.UserID) AS Entitled_Users
from Membership] t
where FirstName not like '%test%' and LastName not like '%test%' 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 current_member_of_SecGroup like 'yes' and UserID is not null and WhenAddedToGroup is not null
)
SELECT * FROM Cnt
***SQL born on date Spring 2013:-)
December 11, 2015 at 7:38 pm
Hi Thomas,
I found out the reason why the total is 525 using the count distinct of SQL, it is because there are USERID's that are a member of multiple security groups (i.e. SecuriyGroup1, SecurityGroup2)....The reason mine is 495 is because I used the Count Distinct of SSRS Report Builder and it seems report builder count distinct only count the USERID once even if the USERID is a member of multiple security groups (i.e SecurityGroup1, SecurityGroup2)...
it seems that yours is more accurate than mine...I am not sure if that makes sense...
December 11, 2015 at 8:05 pm
Hi,
One more question please, is there a way to combine Month Year in one column/row ?
YrMnth | Running_Total
Jan-2015 | 100
Feb-2015 | 135
Mar-2015 | 240
Thanks again in advance.
December 11, 2015 at 9:57 pm
Did i miss it, and this is in a report?
I tweaked your query to turn it into a stored procedure, just to make it easier to call in SSRS.
ALTER PROC [dbo].[SummarizeEvents]
AS
BEGIN
WITH Cnt AS(
SELECT COUNT(DISTINCT t.UserID) AS Value, DATEFROMPARTS(YEAR(t.When_Added_To_Sec_Group),MONTH(t.When_Added_To_Sec_Group),1) As FirstOfMonth
FROM SomeEvent t
GROUP BY DATEFROMPARTS(YEAR(t.When_Added_To_Sec_Group),MONTH(t.When_Added_To_Sec_Group),1)
)
SELECT FirstOfMonth, Value ,
(SELECT SUM(t2.Value) AS Value
FROM Cnt T2
WHERE T2.FirstOfMonth <= T1.FirstOfMonth) AS RunningTotal
FROM Cnt T1
END
Then in SSRS, I set the format of the date to "Jan 2000" (MMM yyyy).
December 11, 2015 at 10:21 pm
I think its 2008 so I don't think Datefromparts is kosher here.
***SQL born on date Spring 2013:-)
December 11, 2015 at 11:01 pm
Oops... oh well, gotta leave something for the OP to do!!!
December 12, 2015 at 12:23 am
thanks Guys for all your help, I very much appreciate it. Have a wonderful weekend & God Bless you all!
December 12, 2015 at 6:55 pm
You don't have a "When Removed" column?
--Jeff Moden
December 12, 2015 at 9:40 pm
Here is with year and month
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);
WITH Cnt AS(
SELECT COUNT(DISTINCT t.UserID) AS Value, CONVERT(CHAR(6),t.When_Added_To_Sec_Group, 112) As YrMnth
FROM #Temp t
GROUP BY CONVERT(CHAR(6),t.When_Added_To_Sec_Group, 112)
)
SELECT t1.YrMnth, SUM(t2.Value)
FROM Cnt t1
INNER JOIN Cnt t2 ON T2.YrMnth <= T1.YrMnth
GROUP BY t1.YrMnth
***SQL born on date Spring 2013:-)
December 13, 2015 at 9:58 pm
Hi,
Seeking your help/guidance again please as my SQL skill is not that advance...I trying to combine or join the two SQL query below (I guess the first one is called CTE)...
WITH Cnt AS (
select Count(Distinct UserID) as Entitled_Users, DATEFROMPARTS(YEAR(t.WhenAddedToGroup),MONTH(t.WhenAddedToGroup),1) as When_Added_To_Group
from [GLOBAL].[dbo].[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)
)
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
second SQL query that I want to join or combine to the CTE above...
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
I tried to join concurrent_Date and When_Added_To_Group but no luck... Output of the second SQL query are:
EventType|Concurrent_Date|Peak_Concurrent_Users|Hub
Max_User|1/12/2015 12:00:00AM|112|Hub1
Max_User|1/11/2015 12:00:00AM|124|Hub1
Max_User|1/10/2015 12:00:00AM|109|Hub1
Max_User|1/09/2015 12:00:00AM|135|Hub1
Thanks again for your help.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply