CODEWITHSUNDEEP
sqlsql-serverdategroup-by
ManiMuthuPandi
ManiMuthuPandi

Reputation: 1604

Get count of users till a given date

I am having below two tables used in the join

TABLE1 : rat_proj_duration_map
Columns: UID,Hour_val,month_val

TABLE2 : users
Columns: UID, status, left_on

Here Status will contain texts('Act','InAct')

Now, I am trying to get count of UIDs grouped by month_val field as like below

select count(r.uid) as cnt, r.month_val
from rat_proj_duration_map r
    inner join users u on r.uid = u.uid 
where  r.year_val = 2018 
group by r.month_val
order by month_val

The above Query gives count of the records in month wise.

But, I am trying to ignore the 'Inactivated' users (Status field in users table) from the date(left_on from users table) of left.

So if the user was left on '2018-03-27', then that user record should be counted upto march month, not after that.

What's the condition to be added to my Query

Example Output: If there are 100 users records and one user left_on '2018-03-27' with status 'Inactivated' then my result should look like below

Count  Month_val
100      1
100      2
100      3
99       4
99       5
99       6
99       7
99       8
99       9
99       10
99       11
99       12

Upvotes: 0

Views: 92

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272096

I think you want this:

SELECT COUNT(CASE WHEN u.status = 'InAct' AND u.left_on < DATEADD(month, month_val - 1, DATEADD(year, year_val - 1900, 0)) THEN NULL ELSE 1 END) AS cnt, r.month_val
FROM rat_proj_duration_map r
INNER JOIN users u ON r.uid = u.uid
WHERE r.year_val = 2018
GROUP BY r.year_val, r.month_val
ORDER BY r.year_val, r.month_val

The expression DATEADD(month, month_val - 1, DATEADD(year, year_val - 1900, 0)) will convert 2018, 3 to 2018-03-01. You need to NOT count those records where user status is a inactive and user left earlier than year-month-01.

SQL Fiddle with dummy data

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37357

You could try this query (necessary comments in code):

select [count] - [left_count] from 
(
    select [left_month_val],
           --this will give amount of users left UP TO particular month
           SUM([left_count]) over (order by [left_month_val] rows between unbounded preceding and current row) [left_count]
    from (
        --here you count how many users left in particular months
        select DATEPART(month, [left_on]) [left_month_val], COUNT(*) [left_count]
        from [users]
        group by DATEPART(month, [left_on])
    ) [a]
) [u] join (
    --here you count how many users were there in particular months
    select [month_val], count(*) [count]
    from rat_proj_duration_map
    group by [month_val]
) [r] on [u].[left_month_val] = [r].[month_val]

Also, keep in mind that none of the query includes year, so you should add another grouping variable. Unfortunately, I couldn't try this myself, as you didn't give example data.

Upvotes: 1

EzLo
EzLo

Reputation: 14189

Filter the users by each project depending on their disable date. You can do this with an additional joining clause (or in the WHERE in this case).

DECLARE @FilterYear INT = 2018

select 
    count(r.uid) as cnt, 
    r.month_val
from 
    rat_proj_duration_map r
    inner join users u on r.uid = u.uid 
where  
    r.year_val = @FilterYear AND
    (
        u.left_on IS NULL OR
        YEAR(u.left_on) > @FilterYear OR
        (YEAR(u.left_on) = @FilterYear AND R.month_val <= MONTH(u.left_on))
    )
group by 
    r.month_val
order by 
    month_val

Keep in mind that we are comparing year and months (without days). You will have to consider what happens when the user is deactivated in the middle of the month.

Upvotes: 2

Tufan Chand
Tufan Chand

Reputation: 752

You can try this

select count(case when u.status='Act' then r.uid end) as cnt, r.month_val
from rat_proj_duration_map r
inner join users u on r.uid = u.uid 
where  r.year_val = 2018 
group by r.month_val
order by month_val

Upvotes: -1

Related Questions