3

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?

6

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;
}
| improve this answer | |
  • Looks good! Thank you. I figured I would need to use a for loop, but your while is really smart. – Frederik Witte Jun 8 '15 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.