MS SQL: Conditional CASE, WHEN for GROUPED records

Question

I tried to solve the below problem using CASE/WHEN, but am stuck in getting the exact solution. Below is the problem followed by expected result. Need your help guys.

Consider the below table EmpComp

NAME        COMPANY        START_DATE        END_DATE        IS_ACTIVE
-----------------------------------------------------------------------
Kiran       TCS            01-Apr-2015       31-May-2015     0
Kiran       TCS            15-Apr-2015       31-May-2015     1
Kiran       TCS            15-Jun-2015       30-Jun-2015     1
Mike        ABC            01-Jan-2015       31-Jan-2015     0
Mike        ABC            01-Feb-2015       31-Mar-2015     0
Pawan       XYZ            01-Dec-2015       31-Dec-2015     1

Result should be: The logic is explained after the result tables one more time.

NAME        COMPANY        START_DATE        END_DATE        IS_ACTIVE
-----------------------------------------------------------------------
Kiran       TCS            15-Apr-2015       30-Jun-2015     1 (only the records with 1 are considered and start_date, end_date should cover the complete end to end range for is_active = 1, incld any gap days.
Mike        ABC            01-Feb-2015       31-Mar-2015     0 (when all the grouped records have is_active = 0, only the last one is shown)
Pawan       XYZ            01-Dec-2015       31-Dec-2015     1

OR

NAME        COMPANY        START_DATE        END_DATE        IS_ACTIVE
-----------------------------------------------------------------------
Kiran       TCS            15-Apr-2015       30-Jun-2015     1 (same logic as above for some of grouped records with is_active = 1)
Mike        ABC            01-Jan-2015       31-Jan-2015     0 (it is ok to show all the grouped records that have all 0s for is_active)
Mike        ABC            01-Feb-2015       31-Mar-2015     0
Pawan       XYZ            01-Dec-2015       31-Dec-2015     1

Logic:

• The records are grouped by combination for Name/Company
• For each grouping when there are some records with IS_ACTIVE flag set to 1, then ignore the records of the group that have IS_ACTIVE = 0,
• display the START_DATE, END_DATE to cover the complete range of dates for IS_ACTIVE = 1.
• If the grouping has all 0s, then it's OK to show all the records as is or better be able to show only the last record with 0. otherwise if there are is only one/all record(s) with 0s, keep them as it is

Answer 1

You can do it using view and Union Query

1: Create view that contain records with 1

**

Create View My_View as 
Select NAME,COMPANY,MIN(STAR_DATE) as START_DATE ,MAX(END_DATE) as END_DATE
From MY_TABLE
Where IS_ACTIVE=1 group by NAME,COMPANY

**

MY_VEW contains the records that have 1 and actually it is a table and every time your table changed its atomically changed

2: make 2 SQLS using UNION

     2.1 :The first display records from our view  .

    2.2: Second display records with 0 that their key not exist in our view

**

Select * from My_View (First Query 
(In case that you do not want all records in view you can use where exactly like table)
Union 
Select A. NAME,A.COMPANY,A.START_DATE,A.END_DATE   
from My_Table as A left join MY_View as B on A.Name=B.NAME
And  A.COMPANY=B.COMPANY where B.NAME IS NULL and B.COMPANY IS_NULL

**

Answer 2

Using aggregation functions with CASE expressions:

SELECT
    Name,
    Company,
    Start_Date =
        CASE
            WHEN SUM(CASE WHEN Is_Active = 1 THEN 1 ELSE 0 END) > 0 
                THEN MIN(CASE WHEN Is_Active = 1 THEN Start_Date END)
            ELSE
                MAX(Start_Date)
        END,
    End_Date =
        CASE
            WHEN SUM(CASE WHEN Is_Active = 1 THEN 1 ELSE 0 END) > 0 
                THEN MAX(CASE WHEN Is_Active = 1 THEN End_Date END)
            ELSE
                MAX(End_Date)
        END,
    Is_Active = 
        CASE
            WHEN SUM(CASE WHEN Is_Active = 1 THEN 1 ELSE 0 END) > 0  THEN 1
            ELSE 0
        END
FROM #EmpComp 
GROUP BY
    Name, Company
ORDER BY
    Name, Company

DEMO