SQLTeam.com | Weblogs | Forums

Pivot query with rolling annual values

#1

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

#2

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)
#3

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

#4

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