Select the last consecutive days SOQL?

Question

Is it possible, with SOQL only, to select something for a consecutive date? Expect following table(where 2012-01-07 is today):

User  LoginDate      Whatever
 1,   2012-01-01,    0
 1,   2012-01-02,    2
 3,   2012-01-03,    0
 4,   2012-01-04,    0
 5,   2012-01-05,    0
 6,   2012-01-06,    5
 1,   2012-01-07,    5

Now the query should return "1" for user 1, as the last time the user logged in, there were no consecutive logins before. If we have a table like this:

    User  LoginDate      Whatever
     1,   2012-01-01,    0
     1,   2012-01-02,    2
     1,   2012-01-03,    0
     1,   2012-01-04,    0
     1,   2012-01-05,    0
     1,   2012-01-06,    5
     1,   2012-01-07,    5

It should return "7", as the user was logged in for 7 consecutive days until today. When the user didn't login yesterday, it should return 0.

I sadly only know the basics of SOQL and have no idea where to read up on this. Does someone have a code sample or a link about this topic, if it is possible at all?

Answer 1

This is not possible with only SOQL. You will need to do some post-processing in Apex or elsewhere if you want this information. For example if you want the current consecutive logins, you would want something like the below.

public static Integer getConsecutiveLogins(Id userId)
{
    Set<Date> loginTimes = new Set<Date>();
    for (LoginHistory login : [SELECT LoginTime FROM LoginHistory WHERE UserId = :userId])
    {
        loginTimes.add(login.LoginTime.date());
    }

    Integer consecutiveLogins = 0;
    while (loginTimes.contains(Date.today().addDays(-consecutiveLogins)))
    {
        consecutiveLogins++;
    }
    return consecutiveLogins;
}