Unable to get total count of users in the user group per month in SQL Server

Question

I have four different tables user, user_group, user_and_groups and actions.

Users can be added/removed from user_group. so one user_group may contain many users. Table user_and_groups is a relationship table between user and user_group. Actions table contains records actions for user added/removed to user_group and action(.i.e. added/removed) performed date. So for every month users count in user_group may vary.

I am using SQL Server and I want to list the total count of users in the user group per month.

Below table column details:

User Table-> userid, details

user_group Table -> groupid, details

user_and_groups Table -> userid, groupid (many to many)

actions Table -> **actionid, action_type, directid, indirectid, action_date**
where directid is user_group_Id, and indirectid is userid.

**Action table dummy data:**

**|actionid|action_type          |directid|indirectid|action_date|**

|1 |     user_added_in_group   |100      |2001      |1-Jan-2012|

|2 |     user_added_in_group   |100      |2002      |1-Feb-2012|

|3 |     user_added_in_group   |100      |2003      |1-March-2012|

|4 |     user_removed_From_group |100    |2001      |1-March-2012|

|5 |     user_added_in_group    |100     |2004      |1-April-2012|

|6 |     user_added_in_group    |100     |2001      |1-April-2012|

Notice in above table userid=2001 was first added in Jan then removed in march and then re-added in April.

I want output for the user_group_id = 100 per month- total count of users.

**Expected output:**

+------+-------+-------+

| Year | Month | Count |

+------+-------+-------+

| 2011 | 12    | 0     |

| 2012 | 01    | 1     |

| 2012 | 02    | 2     |

| 2012 | 03    | 2     |

| 2012 | 04    | 4     |

I am not able to write the SQL query for this. Has anybody written same query?

/****************** start edit *************/**

I think I am very near to answer so posting here the query:

    DECLARE @countOfUsersInGroup INT = 223 
    --(this count is got from user_and_groups relation ship table. which will give count of users in one group at current date)

-- but we need each month's user count in user group - do following


DECLARE  @addRemoveActionsPerMonth TABLE (id INT IDENTITY ,cyear INT, cmonth INT, addActionsInMonth INT, removedActionsInMonth INT, sumOfAddNRemovedActionsInMonth INT)
INSERT INTO @addRemoveActionsPerMonth (cyear, cmonth, addActionsInMonth, removedActionsInMonth, sumOfAddNRemovedActionsInMonth)
SELECT *
FROM
(
    SELECT YEAR(action_date) as cyear, MONTH(action_date) as cmonth, 
            COUNT( CASE WHEN action_type like 'user_added_in_group' THEN 1  END ) as addActionsInMonth,
            COUNT( CASE WHEN action_type like 'user_removed_From_group' THEN 1  END ) as removedActionsInMonth,              
            COUNT( CASE WHEN action_type like 'user_removed_From_group' THEN 1  END ) - COUNT( CASE WHEN action_type like 'user_added_in_group' THEN 1  END ) as sumOfAddNRemovedActionsInMonth             
    FROM dbo.[action_fact] 
    WHERE direct_object_id = 100 --(group id)
    AND action_type like 'user_added_in_group' or action_type like 'user_removed_From_group'  -- action is added or removed
    GROUP BY year(action_date), Month(action_date)
)
as innerTable
ORDER BY cyear, cmonth


DECLARE  @cumulativeCountPerMonth TABLE (id INT , cumulativeCount INT)
INSERT INTO @cumulativeCountPerMonth (id, cumulativeCount)

    SELECT id, 
        (
            SELECT sum(sumOfAddNRemovedActionsInMonth)
            FROM @addRemoveActionsPerMonth as innerTab 
            WHERE innerTab.id >= outerTab.id 
        ) as cumulativeCount
    FROM @addRemoveActionsPerMonth as outerTab



SELECT  actionsData.id, cyear, cmonth, addActionsInMonth, removedActionsInMonth, sumOfAddNRemovedActionsInMonth, cumulativeCount,
        @countOfUsersInGroup + cumulativeCount as actualUserCountInMonth
FROM @addRemoveActionsPerMonth as actionsData
INNER JOIN @cumulativeCountPerMonth as cumulativeData ON actionsData.id = cumulativeData.id

Pretty good question, but: 1. Include the DDL (table definitions) and sample data, and 2. Show what you tried. — John Saunders, Dec 17 '13 at 11:55
Suppose at the end of Oct-2013=> There are 223 users. Actions performed in OCT are:4 added & 1 removed, so count at end of Sept will be 220 (223-4+1)=>220 Actions performed in Sept are: 1 added,2 removed, (220-1+2)=>221 users at end of Aug Actions performed in Aug are: 3 added,4 removed, (220-3+4)=>222 users at end of July like this & so on... for each month end I need users present in User group. Also there is one special case that i need to handle: suppose user:1 was added in august then removed in same month, & readded in the same month. This will be counted as one user only — Jayashree, Dec 18 '13 at 05:47
Not exactly the same, but see if http://stackoverflow.com/questions/808356/how-to-determine-values-for-missing-months-based-on-data-of-previous-months-in-t helps you at all. — John Saunders, Dec 18 '13 at 08:11

score 0 · Accepted Answer · answered Dec 17 '13 at 11:56

0

How about this?

SELECT YEAR (ACTION_DATE), MONTH(ACTION_DATE), COUNT(*)
FROM TABLE_ACTION
GROUP BY YEAR(ACTION_DATE), MONTH(ACTION_DATE);

answered Dec 17 '13 at 11:56

Darshan Mehta

30,102
11
68
102

Thanks Darshan, but this will give just count of actions in each month, not users exist in user group in each month. – Jayashree Dec 17 '13 at 13:21
Do you want to display the months and years for which data is not present? If yes, then what is the range? – Darshan Mehta Dec 17 '13 at 13:50
Suppose at the end of Oct-2013=> There are 223 users. Actions performed in OCT are: 4 added & 1 removed, so count at end of Sept will be 220 (223-4+1)=>220 Actions performed in Sept are: 1 added,2 removed, (220-1+2)=>221 users at end of Aug Actions performed in Aug are: 3 added,4 removed, (220-3+4)=>222 users at end of July like this & so on... for each month end I need users present in User group. Also there is one special case that i need to handle: suppose user:1 was added in august then removed in same month, & readded in the same month. This will be counted as one user only. – Jayashree Dec 18 '13 at 05:45

Unable to get total count of users in the user group per month in SQL Server

1 Answers1