时间戳

首页 > 解决方案 > SQL查询仅根据流体条件返回一行

sql-server - SQL查询仅根据流体条件返回一行

问题描述

我知道这是可能的，但不确定如何设置。基本上我需要为每个员工提取数据，但前提是它满足基于几个不同日期的特定标准。

例如，如果员工在 6 月 1 日之前被分配到一家公司，他们会被自动计算在内。

如果员工在 6 月 1 日之后被分配到一家公司，则只有在他们被分配到该公司的日期之后，他们才被计入该公司（即，他们在 6 月 25 日被分配并在 7 月 1 日进行了审查.. .this 应该被计算在内。例如，如果他们在 6 月 25 日被分配并且审查发生在 6 月 15 日，他们将不计入该员工）

如果员工在 4 月 1 日之前被公司解雇，他们不会被计算在内。如果它们在 4 月 1 日或之后被移除，则计算在内。

因此，关键列是员工-客户表中的审核创建日期、开始日期和结束日期。

我相信这需要是某种类型的子查询，它返回员工与该客户的开始日期，然后根据评估此日期与审查日期的案例声明比较审查日期，但我不确定如何执行此操作.

任何帮助，将不胜感激。

编辑：下面的表结构/数据：

员工-客户表

ID    EmpID   CustID  StartDate   EndDate
1       4       10    10/1/2017   2/21/2018
2       4       11    10/1/2017   7/31/2018
3       4       15    10/1/2017   4/8/2018
4       4       17    6/1/2018    NULL (means still active with this employee)
5       4       19    5/18/2018   NULL

客户资料表

ID    CustID   ActivityDate   Task
1       10       1/13/2018    Review
3       15       4/2/2018     Review
4       17       6/25/2018    Review
5       17       6/13/2018    Client Engagement
6       17       6/29/2018    Client Engagement
7       19       5/25/2018    Client Engagement
8       19       6/28/2018    Review

所以对于这个例子，我想要一个查询，它可以根据条件返回以下客户 ID 和数据：

希望这种解释和分解是有道理的。

更新：以下是表格脚本和预期结果：

CREATE TABLE Cust_Employee(

Cust_Emp_ID int IDENTITY(1,1) NOT NULL,

Cust_ID int NOT NULL,

Emp_ID int NULL,

Start_Date datetime NULL,

End_Date datetime NULL,

CONSTRAINT PK_Cust_Employee PRIMARY KEY CLUSTERED

(

Cust_Emp_ID ASC

)WITH (PAD INEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY

)ON PRIMARY

GO

CREATE TABLE Cust_Data(

Cust_Data_ID int IDENTITY(1,1) NOT NULL,

Cust_ID int NULL,

Activity_Date datetime NULL,

Task VARCHAR(50) NULL

)

CONSTRAINT PK_Client_Data PRIMARY KEY CLUSTERED

(

Cust_Data_ID ASC

)WITH (PAD INEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY

)ON PRIMARY

GO

INSERT INTO Cust_Employee VALUES(4, 10, '10/1/2017', '2/21/2018')

INSERT INTO Cust_Employee VALUES(4, 11, '10/1/2017', '7/31/2018')

INSERT INTO Cust_Employee VALUES(4, 15, '10/1/2017', '4/8/2018')

INSERT INTO Cust_Employee VALUES(4, 17, '6/1/2018', NULL)

INSERT INTO Cust_Employee VALUES(4, 19, '5/18/2018', NULL)

INSERT INTO Cust _Data VALUES(10, '1/13/2018', 'Review')

INSERT INTO Cust _Data VALUES(15, '4/2/2018', 'Review')

INSERT INTO Cust _Data VALUES(17, '6/25/2018', 'Review')

INSERT INTO Cust _Data VALUES(17, '6/13/2018', 'Client Engagement')

INSERT INTO Cust _Data VALUES(17, '6/29/2018', 'Client Engagement')

INSERT INTO Cust _Data VALUES(19, '5/25/2018', 'Client Engagement')

INSERT INTO Cust _Data VALUES(19, '6/28/2018', 'Review')

预期成绩：

在此处输入图像描述

标签： sql-serversubquery

解决方案

我不确定我是否理解您的所有要求。事实上，我遗漏了一些东西，因为我获得的结果与你不完全相同。我准备的代码：

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]

因此，我的查询显示了 Emp 19 的额外行，不确定将消除的条件是什么，如果您澄清自己，我将更正响应。

我发现了另一种更精细的解决方案，它更清晰，并且至少对于提供的数据集非常有效，并且具有易于维护的优点。

首先，我必须认识到对我的要求并不是 100% 明确的，因为这些要求都是基于现实生活中常见的例子。必须清楚地识别必须应用的业务规则以及需要应用的顺序（顺序）。因此，根据我的猜测，我构建了以下解决方案。此解决方案的优点是非常容易调试：

  1. 每个规则都用一个可以在调试模式下显示的数字标识（如果之后需要，则省略），并说明已应用哪个规则
  2. 规则是按顺序应用的，因此，如果一个规则使记录显示，其余规则将不会应用。这是因为检查规则的顺序很重要
  3. 规则的负数表示该规则意味着不应显示记录。

：

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;

这种方法的最好之处在于它计算并显示了已应用的规则，因此，它可以很容易地调试，因为查询显示了已应用的规则。如果规则的顺序或某些规则不正确，可以非常快速地检测到并修复。这同样适用于未来的更改，因为通常这些基于日期的规则确实经常更改，我们需要一种简单的方法来维护代码。最后，我希望这个练习能为未来的发展提供一些想法，因为可追溯性和可支持性在创建代码时非常重要。

推荐阅读