SQL query to only return a row based on fluid conditions

I am not sure if I understood all your request. In fact, I am missing something as the results that I obtain are not exactly the same than you. The code that I have prepared:

SELECT E.Cust_ID AS Emp_ID, E.Emp_ID AS Cust_ID, E.Start_Date, E.End_Date, 
        MAX(D.Activity_Date) AS Activity_Date, D.Task
    FROM Cust_Employee E
    LEFT OUTER JOIN Cust_Data D
        ON E.Emp_ID = D.Cust_ID
    WHERE COALESCE(E.End_Date, GETDATE()) > '20180401'
    GROUP BY 
            E.Cust_ID, E.Emp_ID, E.Start_Date, E.End_Date, 
            D.Task
    ORDER BY E.Cust_ID;[![enter image description here][1]][1]

So, my query shows an extra row for Emp 19, not sure what is the condition that will eliminate, if you clarify myself I will correct the response.

I have found another solution more refined that it is clearer and it works very well at least for the set of data provided with the advantage that it is very easy to maintain.

First, I have to recognise that the requirements for me are not 100% clear because are based in examples what is usual in real life. It is necessary to identify clearly the business rules that have to be applied and the sequence (order) that need to be applied. So, based in my guess I have built the following solution. The advantage of this solution is that it is very easy to debug as:

1. Each rule is identified with a number that can be shown in debug mode (ommited if required afterwards) and is telling which rule has been applied
2. The rules are applied sequencially, so, if one rule makes a record to be shown, rest of rules will not be applied. It is because that it is important the order that the rules are checked
3. Negative number for a rule shows that the rule means the record should not be shown.

:

WITH CTE AS (
    SELECT E.Cust_ID AS Emp_ID, E.Emp_ID AS Cust_ID, 
           E.Start_Date, E.End_Date, 
        MAX(D.Activity_Date) AS Activity_Date, D.Task,
        CASE 
            -- RULE -1: Removed Prior to 4/1 cutoff date
            WHEN E.End_Date < '20180401'                        THEN -1

            --  RULE 1: If the employee has had the customer past the 5/31 cutoff date, even though there is no review for the customer
            WHEN E.End_Date > '20180531'                        THEN 1

            --  RULE 2: If the employee had the customer past the 4/1 cutoff date before it was removed from them
            WHEN D.Activity_Date > '20180401' AND D.Activity_Date <= E.End_Date THEN 2

            --   RULE -2: Client engagement from 6/13/2018 does NOT get returned because it happened BEFORE the review was done with this client
            WHEN D.Task = 'Client Engagement' 
             AND NOT EXISTS (SELECT 1 FROM Cust_Data D2 WHERE D2.Cust_ID = E.Emp_ID AND D2.Task = 'Review' AND D2.Activity_Date <= D.Activity_Date)
                THEN -2

            --   RULE 12: If the employee was assigned to a company before 6/1 they get counted automatically.
            WHEN E.Start_Date <= '20180601'                     THEN 12

            --  RULE 14: If EndDate later than June-1-2018
            WHEN  COALESCE(E.End_Date, GETDATE()) > '20180601'  THEN 14

            -- RULE 0: Other cases
            ELSE 0 

        END AS [Rule]
    FROM Cust_Employee E
    LEFT OUTER JOIN Cust_Data D
        ON E.Emp_ID = D.Cust_ID
        --AND D.Activity_Date > '20180401'

    GROUP BY 
            E.Cust_ID, E.Emp_ID, E.Start_Date, E.End_Date, 
            D.Task, D.Activity_Date
    ) 
SELECT Emp_ID, Cust_ID, Start_Date, End_Date, Activity_Date, Task, [Rule]
    FROM CTE
    WHERE [Rule] > 0
    ORDER BY Cust_ID, Start_Date, Activity_Date;

The best of this method is that it calculates & shows the Rule that has been applied, so, it can be debugged very easily as the query shows which rule has been applied. If the order of rulesor some rule is incorrect, it can be detected very quickly and fixed. The same applies for future changes as normally these rules based on dates do change very often and we need an easy way to maintain the code. Finally, I hope that this exercise will give some ideas for future developments as traceability and supportability is very important when creating code.

For such complex logics, I would advise for CTE statements in order to create subgroups of rows that are eligible to appear in the final result set, in order to have a cleaner query to create/maintain and creating positive/negative rules, like so:

;WITH AssignedBefore as
(
    --if the employee was assigned to a company before 6/1 they get counted automatically.
    SELECT Cust_ID, Emp_ID
    FROM Cust_Employee
    WHERE Start_Date <= '20180601'
),
AssignedReviewed as
(
    --If the employee was assigned to a company after 6/1 they only get counted IF they have a review with that company after the date they were assigned
    SELECT Cust_ID, Emp_ID
    FROM Cust_Employee E
    CROSS APPLY (
        SELECT 1 as Exist
        FROM Cust_Data D
        WHERE D.Cust_ID = E.Cust_ID
        AND D.Task = 'Review'
        AND D.Activity_Date > E.Start_Date
        ) C
    WHERE E.Start_Date > '20180601'
),
RemovedAfter as
(
    --If the employee gets removed from a company before 4/1 they dont get counted. If they are removed on or after 4/1 it counts.
    SELECT Cust_ID, Emp_ID
    FROM Cust_Employee
    WHERE End_Date >= '20180401'
),
RemovedBefore as
(
    --If the employee gets removed from a company before 4/1 they dont get counted. If they are removed on or after 4/1 it counts.
    SELECT Cust_ID, Emp_ID
    FROM Cust_Employee
    WHERE End_Date <= '20180401'
)
--Positive Rules
SELECT * FROM AssignedBefore
UNION
SELECT * FROM AssignedReviewed
UNION 
SELECT * FROM RemovedBefore
--Negative Rules
EXCEPT
SELECT * FROM RemovedBefore

Once you have this result for the tuples of Cust/Emp that need to appear in the output, you can add whatever information you need.

The way I'd approach this query would get very friendly with both common table expressions and EXISTS/NOT EXISTS.

I gathered for a given employee, if there were engagements with the customer prior to when the employee had a review with that customer, they should be ignored in the scope of that employee. For this I've implemented a common table expression (cte_engagements_to_ignore) to filter those out. The results of this common table expression end up being employee / cust_data records that should be ignored. It works by first filtering down to all the engagements, and then filtering down to just the ones where no previous review exists that happened after the employee's assignment and prior to the engagement we're comparing to.

Next up we query the employee/customer data tables and automatically include if the customer started prior to 6/1 OR if a review exists that happened after they were assigned to the employee. We then exclude if unassigned prior to 4/1 or if the engagement is one that we identified should be ignored for the given employee.

Very puzzling one, indeed!

WITH cte_engagements_to_ignore AS 
    ( -- filter out client engagements that happened prior to reviews
        SELECT 
            A.Emp_ID,
            B.Cust_Data_ID
        FROM Cust_Employee A 
        INNER JOIN Cust_Data B 
        ON      A.Cust_ID = B.Cust_ID
        WHERE   B.Task = 'Client Engagement' 
            AND NOT EXISTS 
                    ( -- exclude this client engagement if there was not a review for this customer prior to it
                        SELECT
                            *
                        FROM Cust_Data X1 
                        WHERE   A.Cust_ID = X1.Cust_ID
                            AND X1.Task = 'Review'
                            AND A.Start_Date < X1.Activity_Date -- review happened after assignment
                            AND B.Activity_Date > X1.Activity_Date -- review happened prior to engagement
                    )
    )
SELECT 
    A.Emp_ID,
    A.Cust_ID,
    A.Start_Date,
    A.End_Date,
    B.Activity_Date,
    B.Task
FROM Cust_Employee A 
LEFT JOIN Cust_Data B 
ON      A.Cust_ID = B.Cust_ID
WHERE   (
            -- included automatically if started before 6/1
            A.Start_Date < '2018-06-01' 
            -- or include if there is a review after assignment
        OR EXISTS 
                ( 
                    SELECT 
                        *
                    FROM Cust_Data X1 
                    WHERE   A.Cust_ID = X1.Cust_ID
                        AND A.Start_Date < X1.Activity_Date
                        AND X1.Task = 'Review'
                )
        )
        -- exclude if unassigned prior to 4/1
    AND ISNULL(A.End_Date, '2050-01-01') >= '2018-04-01'
        -- filter out engagements we identified should be ignored
    AND NOT EXISTS 
        (
            SELECT 
                *
            FROM cte_engagements_to_ignore X1 
            WHERE   A.Emp_ID = X1.Emp_ID
                AND B.Cust_Data_ID = X1.Cust_Data_ID
        )

My approach to this to get the various rule criteria using CTEs and then apply the logic in the final query:

WITH Reviews AS (
    SELECT d1.Cust_ID, 'true' AS HasActiveReview
    FROM Cust_Data d1
    INNER JOIN Cust_Data d2 ON d1.Cust_ID = d2.Cust_id
    WHERE d1.Task = 'Review' 
        AND d2.Task = 'Client Engagement'
        AND d1.Activity_Date >= d2.Activity_Date
    ),
RuleData as (
SELECT e.Cust_Emp_ID,
    (CASE WHEN e.Start_Date >= '20180601' THEN 'true' ELSE 'false' END) AS StartAfter0601,
    (CASE WHEN e.End_Date <= '20180401' THEN 'true' ELSE 'false' END) as EndBefore0401,
    COALESCE(r.HasActiveReview, 'false') as HasReview
FROM Cust_Employee e
LEFT OUTER JOIN reviews r on e.Cust_ID = r.Cust_ID)
SELECT e.Emp_id, e.Cust_id, e.Start_Date, e.end_date, MAX(d.Activity_Date) AS Activity_Date, d.Task
FROM RuleData r
INNER JOIN Cust_Employee e on r.Cust_Emp_ID = e.Cust_Emp_ID
LEFT OUTER JOIN Cust_Data d on e.Cust_ID = d.Cust_ID
WHERE r.EndBefore0401 = 'false'
    AND ((r.StartAfter0601 = 'true' AND r.HasReview = 'true') OR r.StartAfter0601 = 'false')
GROUP BY e.Emp_id, e.Cust_id, e.Start_Date, e.end_date, d.Task, r.Cust_Emp_ID, r.StartAfter0601, r.EndBefore0401, r.HasReview
ORDER BY e.Emp_id, e.Cust_id;

If you need to debug it is easy to add the rule data to end of the query to see why a row is returned:

WITH Reviews AS (
    SELECT d1.Cust_ID, 'true' AS HasActiveReview
    FROM Cust_Data d1
    INNER JOIN Cust_Data d2 ON d1.Cust_ID = d2.Cust_id
    WHERE d1.Task = 'Review' 
        AND d2.Task = 'Client Engagement'
        AND d1.Activity_Date >= d2.Activity_Date
    ),
RuleData as (
SELECT e.Cust_Emp_ID,
    (CASE WHEN e.Start_Date >= '20180601' THEN 'true' ELSE 'false' END) AS StartAfter0601,
    (CASE WHEN e.End_Date <= '20180401' THEN 'true' ELSE 'false' END) as EndBefore0401,
    COALESCE(r.HasActiveReview, 'false') as HasReview
FROM Cust_Employee e
LEFT OUTER JOIN reviews r on e.Cust_ID = r.Cust_ID)
SELECT e.Emp_id, e.Cust_id, e.Start_Date, e.end_date, MAX(d.Activity_Date) AS Activity_Date, d.Task, r.Cust_Emp_ID, r.StartAfter0601, r.EndBefore0401, r.HasReview
FROM RuleData r
INNER JOIN Cust_Employee e on r.Cust_Emp_ID = e.Cust_Emp_ID
LEFT OUTER JOIN Cust_Data d on e.Cust_ID = d.Cust_ID
WHERE r.EndBefore0401 = 'false'
    AND ((r.StartAfter0601 = 'true' AND r.HasReview = 'true') OR r.StartAfter0601 = 'false')
GROUP BY e.Emp_id, e.Cust_id, e.Start_Date, e.end_date, d.Task, r.Cust_Emp_ID, r.StartAfter0601, r.EndBefore0401, r.HasReview
ORDER BY e.Emp_id, e.Cust_id;

I have used 'true' and false' to represent boolean values, you could use 1 and 0 BIT if you prefer.

Running the query it returns an extra row for Cust_Id 19, returning both the 'Customer Engagement' record plus the 'Review' one. I'm not sure why it shouldn't do this when you want both rows for Cust_Id 17 and it seems the same should apply for Cust_Id 19

Emp_Id,Cust_id,Start_Date,End_Date,Activity_Date,Task
4,11,2017-10-01 00:00:00.000,2018-07-31 00:00:00.000,NULL,NULL
4,15,2017-10-01 00:00:00.000,2018-04-08 00:00:00.000,2018-04-02 00:00:00.000,Review
4,17,2018-06-01 00:00:00.000,NULL,2018-06-29 00:00:00.000,Client Engagement
4,17,2018-06-01 00:00:00.000,NULL,2018-06-25 00:00:00.000,Review
4,19,2018-05-18 00:00:00.000,NULL,2018-05-25 00:00:00.000,Client Engagement
4,19,2018-05-18 00:00:00.000,NULL,2018-06-28 00:00:00.000,Review

Maybe this will help.

SELECT employee
FROM employee emp
JOIN employee-customer ecust ON ecust.empID = ecust.empID
JOIN customer cust ON cust.custID = ecust.custID and cust.Task = 'Review'
WHERE 
(emp.StartDate <= '01062019' and (emp.EndDate <= '01042019' or emp.EndDate = NULL) or 
( emp.StartDate <= cust.ActivityDate )