SQL active users by month-CodePudding

I would like to know the number of active users by month, I use SQL Server 2017.

I have an AuditLog table like:

- UserID: int
- DateTime: datetime
- AuditType: int

UserID  DateTime     AuditType
------------------------------
1       2022-01-01   1
1       2022-01-15   4
1       2022-02-20   3
2       2022-01-10   8
2       2022-03-10   1
3       2022-03-20   1

If someone has at least one entry in a given month then he/she is treated as active. I would like to have a result like:

Date     Count
2022-01  2
2022-02  1
2022-03  2

CodePudding user response：

I think you can combine the function Month(datetime) in the GROUP BY with the Count function SELECT COUNT(UserID)

CodePudding user response：

SELECT (CAST(YEAR(C.DATE)AS CHAR(4)) '-' CAST(MONTH(C.DATE)AS CHAR(2)))YEAR_MONTH,COUNT(C.USER_ID)CNTT
FROM AUDITLOG AS C
GROUP BY (CAST(YEAR(C.DATE)AS CHAR(4)) '-' CAST(MONTH(C.DATE)AS CHAR(2)))
ORDER BY (CAST(YEAR(C.DATE)AS CHAR(4)) '-' CAST(MONTH(C.DATE)AS CHAR(2)));

CodePudding user response：

Here is solutions,

Select [Date],count(1) as Count From (
select Cast(cast(d.DateTime as date) as varchar(7)) as [Date],UserId 
from AuditLog d 
Group by Cast(cast(d.DateTime as date) as varchar(7)),UserId
) as q1 Group by [Date]
Order by 1

Hope, it will works.

CodePudding user response：

GROUP DATE (Year and Month) either combine or separate and count distinct userId

SELECT  CONVERT(VARCHAR(7), [DateTime], 126)[Date], COUNT(DISTINCT UserID)[Count] 
FROM AuditLog
GROUP BY CONVERT(VARCHAR(7), [DateTime], 126)