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
What tells you that a user is new for a month? The status? Remember, we know NOTHING about your data, so what may be "obvious" to you is not to us.
Also, please provide table names/aliases on ALL columns in the query, i.e., fill in the ?s below.
select
count(distinct(u.id)) as uniqueusercompletions,
DATEADD(YEAR, DATEDIFF(YEAR, 0, ?.DateCompleted), 0) as datecompleted,
c.Name [companyname]
from courseenrollments ce
inner join users u on ce.UserId = u.id
inner join clients c on c.id = u.clientid
where ?.status = 3 and
?.datecompleted > getdate()-1000 and ?.DateCompleted < getdate()-2 and
?.portalstatus = 0 and ?.PortalType = 0 and
c.isdeleted = 0 and ce.IsHistoricalImported = 0
group by c.Name, DATEADD(YEAR, DATEDIFF(YEAR, 0, ?.DateCompleted), 0)
Hi Scott.
I removed extraneous clauses that aren't relevant and filled in the remaining question marks. I need to know how many distinct users completed a course in each time frame. So for...
Feb 1 2018 - Feb 1 2019 <-- all of the users that have an entry in CourseEnrollments where CourseEnrollments.DateCompleted falls within that date range.
select companyname,
[2018-01-01],
[2019-01-01],
[2020-01-01]
FROM
(
select count(distinct(users.id)) as uniqueusercompletions, DATEADD(YEAR, DATEDIFF(YEAR, 0, CourseEnrollments.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 CourseEnrollments.datecompleted > getdate()-1000 and CourseEnrollments.DateCompleted < getdate()-2
group by clients.Name, DATEADD(YEAR, DATEDIFF(YEAR, 0, CourseEnrollments.DateCompleted), 0)
) AS SourceTable
PIVOT
(sum(uniqueusercompletions) FOR datecompleted IN (
[2018-01-01],
[2019-01-01],
[2020-01-01]
)) as PivotTable
It's possible to do it one query, but I'd break this into 2 stages to make it easier to insure performance on the rollup part of the query.
Maybe something like this?
IF OBJECT_ID('tempdb.dbo.#ClientMonthUserCount') IS NOT NULL
DROP TABLE #ClientMonthUserCount;
CREATE TABLE #ClientMonthUserCount (
ClientId int NOT NULL,
MonthCompleted date NOT NULL,
UserCount int NOT NULL,
PRIMARY KEY ( ClientId, MonthCompleted )
)
INSERT INTO #ClientMonthUserCount ( ClientId, MonthCompleted, UserCount )
SELECT
users.clientid AS ClientId,
DATEADD(MONTH, DATEDIFF(MONTH, 0, CourseEnrollments.DateCompleted), 0) AS MonthCompleted,
COUNT(DISTINCT users.id) AS UserCount
from courseenrollments
inner join users on CourseEnrollments.UserId = users.id
where CourseEnrollments.datecompleted > getdate()-1000 and CourseEnrollments.DateCompleted < getdate()-2
group by users.clientid, DATEADD(YEAR, DATEDIFF(YEAR, 0, CourseEnrollments.DateCompleted), 0)
SELECT
ClientId,
MonthCompleted AS MonthStart,
UserCount + (
SELECT SUM(UserCount)
FROM #ClientMonthUserCount CM2
WHERE CM2.ClientId = CM1.ClientId AND
CM2.MonthCompleted BETWEEN DATEADD(MONTH, 1, CM1.MonthCompleted) AND DATEADD(MONTH, 11, MonthCompleted ) )
AS UserCount12MonthsForward
FROM #ClientMonthUserCount CM1
ORDER BY ClientId, MonthCompleted