CODEWITHSUNDEEP
sqlms-access
Ned Charles
Ned Charles

Reputation: 5

Get a unique count of users over a rolling 3 month period

So I have a table that has a column for usernames and then a column with the 1st of the month for each time that user logged into our service. I would like to get a unique user count looking a rolling three months backward for every month in the table. So for example the data looks like this:

username  Month
Bob       01/01/2017
Joe       12/01/2017
Sam       01/01/2017
Bob       01/01/2017
Joe       12/01/2017
Frank     02/01/2017
Alice     02/01/2017
John      02/01/2017
Jen       03/01/2017

and I would want my results to look like this:

Month         Rolling3MonthUniqueUserCount
12/01/2017    1
01/01/2017    3
02/01/2017    6
03/01/2017    6

The query (or Multiple queries) have to do two things:

  1. identify the day 3 months back from the date in the table
  2. Get the unique user count within that 3 month period

For the life of me I cant figure out how to do this, and any help would be appreciated. Thanks :)

P.S. Im using access which uses T-SQL. Which slightly limits the functions that you can use.

Upvotes: 0

Views: 1428

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

In MS-Access, the simplest method is going to be correlated subqueries.

First, you need a list of months:

select distinct DateSerial(datecol), Month(datecol), 1)
from t;

Then you can get the list of values using a correlated subquery:

select m.month,
       (select count(*)
        from t
        where t.datecol >= dateadd("m", -3, m.month) and
              t.datecol < m.month
       ) as rolling_count
from (select distinct DateSerial(datecol), Month(datecol), 1) as month
      from t
     ) as m;

This calculates three complete previous months starting on the first. So the count for January, February, and March would be on April 1st. You can adjust the query if you have a different definition in mind.

Does this work in MS Access?

select m.month,
       (select count(*)
        from (select distinct name
              from t
              where t.datecol >= dateadd("m", -3, m.month) and
                    t.datecol < m.month
             ) as x
       ) as rolling_count
from (select distinct DateSerial(datecol), Month(datecol), 1) as month
      from t
     ) as m;

I don't remember the scoping rules for MS Access.

Upvotes: 0

Related Questions