How to get records that not have specific records in another table-CodePudding

Please consider these Tables:

User:

UserId      Name       LastName
--------------------------------
User1       Name1        LName1
User2       Name2        LName2
User3       Name3        LName3
User4       Name4        LName4

and Users Login Logs:

UserId       LogDate      Status
--------------------------------
User1       2022-01-01      1 <--For Successful Login
User1       2022-01-02      0 <--For UnSuccessful Login
User2       2022-01-02      1
User4       2022-02-02      1
User4       2022-03-02      0
User4       2022-05-02      1
User4       2022-05-10      0
User5       2022-05-10      0

No I want to find those users that don't login in specific duration (for example 1 past month). In another point of view I don't want the users that have at least one Successful login log in specific duration.

I wrote some queries but I couldn't check exclude records that have at least one Successful login log. This is a SQL version of the Linq query:

select * 
from Users aa left join Login_Log bb on aa.UserId = bb.UserId
where (bb.LogDate >= DATEADD(month, -1, getdate()) and bb.LogDate <= getdate()) and bb.Status is null

CodePudding user response：

The LogedInUsers CTE have users which have at least one successfull login. Then the final result is user which are not in CTE:

WITH LogedInUsers
AS (SELECT DISTINCT bb.UserId
    FROM Login_Log bb
    WHERE bb.LogDate >= DATEADD(MONTH, -1, GETDATE())
          AND bb.LogDate <= GETDATE()
          AND bb.Status = 1
   )
SELECT aa.*
FROM Users aa
    LEFT JOIN LogedInUsers liu ON aa.UserId = liu.UserId
    WHERE liu.UserId IS NULL;

CodePudding user response：

I don't want the users that have at least one Successful login log in specific duration.

DateTime startPeriod = ...
DateTime endPeriod = ...
IEnumerable<UserLogin> userLogins == ...
IEnumerable<User> Users = ...

Requirement Give me all Users that didn't have any successful login during the time period between and inclusive startPeriod and endPeriod.

First we fetch the UserIds of all users that had at least one successful login during the period

IEnumerable<UserId> idsOfSuccessfulUserLoginDuringPeriod = userLogins

    // only the succesful UserLogins during period:
    .Where(userLogin => userLogin.Status == 1
           && startPeriod <= userLogin.LogDate
           && userLogin.LogData <= endPeriod)

    // select only the UserIds
    .Select(userLogin => userLogin.UserId)

    // and remove duplicates
    .Distinct();

In words: from all attempts to Log on, keep only the succesful attempts during the Period. From the remaining UserLogins select the UserId, and remove duplicates.

If userId is a reference type, you cannot use the default equality comparer in Distinct, you'll have to provide a reference type that compares by value.

Now remove all users that had a successful login during period:

IEnumerable<User> usersThatHadNoSuccessfulLoginDuringPeriod = users.

    .Where(user => !idsOfSuccessfulUserLoginDuringPeriod.Contains(user.UserId));

In words: to get all Users that had no succesfull login during period, start with all Users and keep only those User that have an UserId that is NOT in the collection of UserIds of Users that had a Successfull login during period.

Of course you can write this in one big LINQ statement. This won't improve efficiency. I'm sure it will deteriorate readability.

Simple comme bonjour!