Home How to get only one result from each group based on condition
Reply: 1

How to get only one result from each group based on condition

BYG
1#
BYG Published in 2018-01-11 21:25:59Z

I have a roleHistory table where I keep track of the active and inactive roles for all the employees. This is my table:

Id RoleId UserId  DateAssigned    Active 
-----------------------------------------
 1    6    824     2017-11-08       1   
 2    7    824     2017-11-08       0     
 3    6    833     2017-11-07       1    
 4    7    833     2017-11-07       0
 5    6    888     2017-11-07       0
 6    7    888     2017-11-10       0
 7    6    888     2017-11-11       1
 8    6    890     2017-11-24       0

I want to group it by userId and roleId and if for example userId 824 is active in one role and inactive in another role, I only want to get the active one. If the user is inactive in both roles, I want to get inactive.

This is what I tried so far:

 SELECT DISTINCT 
     CONVERT(varchar, p_Empl.ID) AS Id, 
     (p_empl.Last_Name + p_Empl.First_Name) AS Name, 
     RoleHistory.Active, RoleHistory.RoleId
 FROM  
     p_Empl
 INNER JOIN 
     UserProfile ON UserProfile.EmployeeID = p_Empl.ID
 INNER JOIN 
     RoleHistory ON RoleHistory.UserId = UserProfile.Id
 INNER JOIN 
     webpages_Roles ON Webpages_Roles.RoleID = RoleHistory.RoleID
 WHERE 
     (webpages_Roles.RoleName = 'Project Manager' OR 
      webpages_Roles.RoleName = 'Sales Manager') 
     AND (RoleHistory.Id IN (SELECT MAX(RoleHistory.Id) AS DateUpdated 
                             FROM RoleHistory 
                             GROUP BY UserId,RoleId))  
ORDER BY 
    Name   

This is the results this query returns:

 1    6    824     2017-11-08       1   
 2    7    824     2017-11-08       0     
 3    6    833     2017-11-07       1    
 4    7    833     2017-11-07       0
 6    7    888     2017-11-10       0
 7    6    888     2017-11-11       1
 8    6    890     2017-11-24       0

But what I want to accomplish is this:

 1    6    824     2017-11-08       1     
 3    6    833     2017-11-07       1    
 7    6    888     2017-11-11       1
 8    6    890     2017-11-24       0

Can anyone help me to achieve that?

Von Aaron
2#
Von Aaron Reply to 2018-01-11 23:35:12Z

You can use a ROW_NUMBER() to determine the active and none active, example

sample data:

        DECLARE @T TABLE
            (
              ID INT ,
              RoleID INT ,
              UserID INT ,
              DateAssigned DATETIME ,
              Active INT
            )


        INSERT  INTO @T
                ( ID, RoleID, UserID, DateAssigned, Active )
        VALUES  ( 1, 6, 824, '2017-11-08', 1 )  
        ,       ( 2, 7, 824, '2017-11-08', 0 )    
        ,       ( 3, 6, 833, '2017-11-07', 1 )   
        ,       ( 4, 7, 833, '2017-11-07', 0 )
        ,       ( 5, 6, 888, '2017-11-07', 0 )
        ,       ( 6, 7, 888, '2017-11-10', 0 )
        ,       ( 7, 6, 888, '2017-11-11', 1 )
        ,       ( 8, 6, 890, '2017-11-24', 0 )

query:

        SELECT  ID ,
                RoleID ,
                UserID ,
                DateAssigned ,
                Active
        FROM    ( SELECT    ID ,
                            RoleID ,
                            UserID ,
                            DateAssigned ,
                            Active ,
                            ROW_NUMBER() OVER ( PARTITION BY UserID ORDER BY Active DESC ) ctr
                  FROM      @T
                ) t
        WHERE   t.ctr = 1

Result

    ID          RoleID      UserID      DateAssigned            Active
    ----------- ----------- ----------- ----------------------- -----------
    1           6           824         2017-11-08 00:00:00.000 1
    3           6           833         2017-11-07 00:00:00.000 1
    7           6           888         2017-11-11 00:00:00.000 1
    8           6           890         2017-11-24 00:00:00.000 0

    (4 row(s) affected) 

In your case, something like this. (Assuming your current result is based on your select query)

         SELECT Id ,
                Name ,
                ACTIVE
         FROM   ( SELECT    Id ,
                            Name ,
                            ACTIVE ,
                            ROW_NUMBER() OVER ( PARTITION BY UserID ORDER BY Active DESC ) ctr
                  FROM      ( SELECT DISTINCT
                                        CONVERT(VARCHAR, p_Empl.ID) AS Id ,
                                        ( p_empl.Last_Name
                                          + p_Empl.First_Name ) AS Name ,
                                        RoleHistory.Active ,
                                        RoleHistory.RoleId,
                                        UserId 
                              FROM      p_Empl
                                        INNER JOIN UserProfile ON UserProfile.EmployeeID = p_Empl.ID
                                        INNER JOIN RoleHistory ON RoleHistory.UserId = UserProfile.Id
                                        INNER JOIN webpages_Roles ON Webpages_Roles.RoleID = RoleHistory.RoleID
                              WHERE     ( webpages_Roles.RoleName = 'Project Manager'
                                          OR webpages_Roles.RoleName = 'Sales Manager'
                                        )
                                        AND ( RoleHistory.Id IN (
                                              SELECT    MAX(RoleHistory.Id) AS DateUpdated
                                              FROM      RoleHistory
                                              GROUP BY  UserId ,
                                                        RoleId ) )
                            ) T
                ) TT
         WHERE  tt.ctr = 1
         ORDER BY TT.Name
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302303 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO