Conditional select in SQL Statement

Question

I'm trying to do something like this:

SELECT UserID from Users as user
    INNER JOIN UserStatus as userStatus on u.UserID = us.UserID
WHERE
    --user.IsActive = 1
    --if user.IsActive = 0
        --userStatus.DeactivatedDate > @StartDate
        --userStatus.DeactivatedDate < @EndDate

So in general I want a query that gets me all results whose value matches the where statement without necessarily ignoring all of those results that do not. Kind of like a Venn diagram with the the left side of the circle and the middle filled in. For the example I've shown, I want all users who are currently active and the users who are not active but were active within the given time frame (if I wanted the total number of users active at any point within a month, for instance).

I've tried doing things like

SELECT UserID from Users as user
    INNER JOIN UserStatus as userStatus on u.UserID = us.UserID
WHERE
    Case user.IsActive = 1

But that is obviously incorrect syntax. I will also need to translate this to LINQ as well, so resources on that would be beneficial as well.

Khan · Accepted Answer · 2013-09-25 14:05:38Z

4

I believe this will give you the result you're looking for:

SELECT  UserID 
FROM    Users U
JOIN    UserStatus US 
    ON  U.UserID = US.UserID
WHERE
        U.IsActive = 1
        OR (
                U.IsActive = 0
            AND US.DeactivatedDate > @StartDate
            AND US.DeactivatedDate < @EndDate
        )

answered Sep 25, 2013 at 14:05

Khan

18.2k5 gold badges52 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Suraj Singh · Accepted Answer · 2013-09-25 14:21:03Z

3

SELECT UserID from Users as user
    INNER JOIN UserStatus as userStatus on u.UserID = us.UserID
WHERE
     user.IsActive = 1 OR us.DeactvatedDate BETWEEN '2012-08-13' AND '2013-08-13'

This will do the job if you have any entry regarding date when a user got decativated.

In LINQ

var UID= from u in Users
         from s in UserStatus
         where u.UserID == s.UserID || (s.DeactivatedDate  >= @StartDate &&        
         s.DeactivatedDate <= @endDate)
         select u.userID

edited Sep 25, 2013 at 14:21

answered Sep 25, 2013 at 14:05

Suraj Singh

4,0871 gold badge23 silver badges37 bronze badges

1 Comment

Khan Over a year ago

Hehe @ decavitated, whatever it means, it sounds deadly ;)

Collectives™ on Stack Overflow

Conditional select in SQL Statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related