report required for active and inactive with the query added

[Growit]

below query need change to get the active and inactive users in that particular application

SELECT ep.endpointname,
Date_format(a.created_on, '%Y.%m') AS Month,
Count(DISTINCT ua.userkey) AS user_count
FROM user_accounts ua
JOIN accounts a
ON ua.accountkey = a.accountkey
JOIN endpoints ep
ON ep.endpointkey = a.endpointkey
WHERE ep.endpointname = 'MyLearning_External'
GROUP BY Date_format(a.created_on, '%Y.%m')
ORDER BY month ASC

from last april 2023 to this month 2024 users who accessed and created in the application

output

above has the data and required the users who are active and inactive.

Could you please split it in two columns with information how many was active and how many was inactive

[rushikeshvartak]

SELECT ep.endpointname,
Date_format(a.created_on, '%Y.%m') AS Month,
Count(DISTINCT ua.userkey) AS user_count,
a.Status
FROM user_accounts ua
JOIN accounts a
ON ua.accountkey = a.accountkey
JOIN endpoints ep
ON ep.endpointkey = a.endpointkey
WHERE ep.endpointname = 'ActiveDirectory_Offshore'
GROUP BY Date_format(a.created_on, '%Y.%m'),a.status
ORDER BY month ASC

[Amit_Malik]

This one will give it in two rows

SELECT ep.endpointname,
Date_format(a.created_on, '%Y.%m') AS Month,
Count(DISTINCT ua.userkey) AS user_count, a.status
FROM user_accounts ua
JOIN accounts a
ON ua.accountkey = a.accountkey
JOIN endpoints ep
ON ep.endpointkey = a.endpointkey
WHERE ep.endpointname = 'MyLearning_External'
GROUP BY Date_format(a.created_on, '%Y.%m'),a.status
ORDER BY month ASC

Something like this will give you in two columns 

select a.endpointname,a.month_a,a.user_count_a,b.user_count_b from (SELECT ep.endpointname,
Date_format(a.created_on, '%Y.%m') AS Month_a,
Count(DISTINCT ua.userkey) AS user_count_a
FROM user_accounts ua
JOIN accounts a
ON ua.accountkey = a.accountkey
JOIN endpoints ep
ON ep.endpointkey = a.endpointkey
WHERE ep.endpointname = 'Delinea Preprod' and a.created_on is not null
GROUP BY Date_format(a.created_on, '%Y.%m')) a
LEFT JOIN
(SELECT ep.endpointname,
Date_format(a.created_on, '%Y.%m') AS Month_b,
Count(DISTINCT ua.userkey) AS user_count_b
FROM user_accounts ua
JOIN accounts a
ON ua.accountkey = a.accountkey
JOIN endpoints ep
ON ep.endpointkey = a.endpointkey
WHERE ep.endpointname = 'Delinea Preprod' and a.created_on is not null and a.status in ('1','manually provisioned')
GROUP BY Date_format(a.created_on, '%Y.%m')) b
ON a.endpointname=b.endpointname and a.Month_a=b.Month_b

[Growit]

need to show wether is is active and inactive , data should show active or inactive

[Amit_Malik · Accepted solution]

select a.endpointname,a.month_a,a.user_count_a as Toatal_Count,b.user_count_b as Active_Count, a.user_count_a-b.user_count_b as Non_Active from (SELECT ep.endpointname,
Date_format(a.created_on, '%Y.%m') AS Month_a,
Count(DISTINCT ua.userkey) AS user_count_a
FROM user_accounts ua
JOIN accounts a
ON ua.accountkey = a.accountkey
JOIN endpoints ep
ON ep.endpointkey = a.endpointkey
WHERE ep.endpointname = 'CRB_AD_DEV' and a.created_on is not null
GROUP BY Date_format(a.created_on, '%Y.%m')) a
LEFT JOIN
(SELECT ep.endpointname,
Date_format(a.created_on, '%Y.%m') AS Month_b,
Count(DISTINCT ua.userkey) AS user_count_b
FROM user_accounts ua
JOIN accounts a
ON ua.accountkey = a.accountkey
JOIN endpoints ep
ON ep.endpointkey = a.endpointkey
WHERE ep.endpointname = 'Your Endpoint' and a.created_on is not null and a.status in ('1','manually provisioned')
GROUP BY Date_format(a.created_on, '%Y.%m')) b
ON a.endpointname=b.endpointname and a.Month_a=b.Month_b
where user_count_b is not null