Destruction of users on the basis of membership, its level and validity or membership

Question

We have a database with duplicate user entries, and I need to choose the "best" user based on several factors:

• Members with membership must be selected before those with
• Members have levels, and ceteris paribus, the user must choose the "best" level of membership.
• Users with active membership must be selected in front of users with expired membership.

Based on these conditions, I came up with something like the following query (the current query is too sensitive).

SELECT TOP 1 u.[UserId]
    FROM [dbo].[Users] u
    LEFT OUTER JOIN [dbo].[UserMemberships] um
        ON u.[UserId] = um.[UserId]
    LEFT OUTER JOIN [dbo].[Memberships] m
        ON um.[MembershipId] = m.[MembershipId]
    WHERE u.[Email] = @Email
    ORDER BY m.[Order] ASC, um.[Expires] DESC, u.[Created] DESC

, , . , , "" ( ), , , , . , , ( ) .

, , , , . , ?

Answer 1

- ? Case , , , :

SELECT TOP 1 u.[UserId], 
                Case um.[Expires]
                    When null then 9999      --Inactive Membership
                    When >= GetDate() then 1 --Expired Membership
                    Else 0                   --Active Membership
                End as ActiveRank, 
                Case m.[Order]
                    When null then 9999      --No Membership
                    Else Order               --Membership Ranking
                End as MembershipRank
  FROM [dbo].[Users] u
  LEFT OUTER JOIN [dbo].[UserMemberships] um
    ON u.[UserId] = um.[UserId]
  LEFT OUTER JOIN [dbo].[Memberships] m
    ON um.[MembershipId] = m.[MembershipId]
  WHERE u.[Email] = @Email             
  ORDER BY ActiveRank ASC, MembershipRank ASC, u.[Created] DESC

. , Created, .

, :

• ActiveRank ... , .
• MembershipRank ( .

Answer 2

psuedocode , :

SELECT TOP 1 FROM (
  SELECT UserId, 1 AS rnk
  FROM Table
  WHERE {most desireable conditions are true}
  UNION ALL
  SELECT UserId, 2 AS rnk
  FROM Table
  WHERE {2nd most desireable conditions are true}
  UNION ALL
  SELECT UserId, 3 AS rnk
  FROM Table
  WHERE {3rd most desireable conditions are true}
  ...
) u
ORDER BY rnk ASC, {Secondary OrderBys like ExpDate etc}

Answer 3

What I did before is using the case argument to provide weight for my various factors. Then you can choose what you want to do based on your total weight. For example:

SELECT TOP 1
    CASE WHEN Expires >= GETDATE() THEN 10 ELSE 0 END
    +
    MemberLevel --Assuming this is some number that already ranks the member based on level
    +
    CASE WHEN IsMember THEN 2 ELSE 0 END MyRank
FROM [Your tables]
ORDER BY 1 Desc