Thursday, July 26, 2012

Get only single record from a group with various conditons

  • If an employee has both eligible and not eligible get the eligible record with min of Cal.
  • If an employee has only eligible or not eligible get the record with min of Cal.
  • For the above both conditions, we should get only one record for each employee.

DECLARE @Temp TABLE(EmpID VARCHAR(50), ISEligible CHAR(1), Cal INT)
        
        INSERT INTO @Temp(EmpID, ISEligible, Cal)
        SELECT '8829', 'Y', 10   UNION ALL
        SELECT '8829', 'N', 3    UNION ALL
        SELECT '8829', 'Y', 8    UNION ALL
        SELECT '1256', 'Y', 0    UNION ALL
        SELECT '1256', 'N', NULL UNION ALL
        SELECT '1111', 'N', NULL UNION ALL
        SELECT '1111', 'N', NULL UNION ALL
        SELECT '2222', 'Y', NULL UNION ALL
        SELECT '2222', 'Y', NULL
        
        SELECT * FROM @Temp
        
        SELECT *
        FROM  ( SELECT  ROW_NUMBER() OVER(PARTITION BY EmpID 
                   ORDER BY ISEligible DESC, Cal asc) RowNum,
                        EmpID     ,
                        ISEligible,
                        Cal
               FROM    @Temp
               )
               AS Temp
       WHERE  RowNum = 1

No comments: