I would like to know the number of active users by month, I use MSSQL 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
>Solution :
I think you can combine the function Month(datetime) in the GROUP BY with the Count function SELECT COUNT(UserID)