The query below works well for giving me distinct user counts for 2018, 2019, and 2020. But what I need is distinct user counts annually rolling forward month by month. So each column would be like this...
Jan 1 2018 - Jan 1 2019
Feb 1 2018 - Feb 1 2019
Mar 1 2018 - Mar 1 2019
etc. etc.
Because these are DISTINCT users completions at the annual level, I can't simply output at the monthly level and sum.
select companyname,
[2018-01-01],
[2019-01-01],
[2020-01-01]
FROM
(
select count(distinct(users.id)) as uniqueusercompletions, DATEADD(YEAR, DATEDIFF(YEAR, 0, DateCompleted), 0) as datecompleted, clients.Name [companyname]
from courseenrollments
inner join users on CourseEnrollments.UserId = users.id
inner join clients on clients.id = users.clientid
where status = 3 and datecompleted > getdate()-1000 and DateCompleted < getdate()-2 and portalstatus = 0 and PortalType = 0 and clients.isdeleted = 0 and courseenrollments.IsHistoricalImported = 0
group by clients.Name, DATEADD(YEAR, DATEDIFF(YEAR, 0, DateCompleted), 0)
) AS SourceTable
PIVOT
(sum(uniqueusercompletions) FOR datecompleted IN (
[2018-01-01],
[2019-01-01],
[2020-01-01]
)) as PivotTable