sql-server - SQL Server : finding gaps in employment

sql-server - SQL Server : finding gaps in employment - island and gap problem

转载作者：行者123 更新时间：2023-12-02 20:59:46

上周我一直在尝试解决堆栈溢出问题，但我仍然无法找到可行的解决方案，所以想知道是否有人可以为我提供一些帮助/建议？

数据结构说明

我有下表:

位置表(zz_position)，用于保存详细信息职位(职位 ID)包括其有效日期范围。

PosNo   Description                Date_From    Date_To 
---------------------------------------------------------
10001   System Administrator       20170101     20231231

资源表(zz_resource)，用于保存资源(员工)的详细信息，包括他们加入公司和离开公司的日期

resID   description  date_from   date_to
------------------------------------------
100     Sam          20160101    20991231
101     Joe          20150101    20991231

就业表 (zz_employment)，用于将职位链接到某个日期范围内的资源

PosNo    resID       Date_From   Date_To     seqNo
---------------------------------------------------
10001    100         20180101    20180401    1
10001    101         20180601    20191231    2
10001    100         20200101    20991231    3

问题

现在，由于人们更换职位，某个职位可能在一段时间内无法填补，我想做的是生成一份报告，我可以使用该报告随时提供职位状态的详细信息及时。

我知道我可以使用日历表生成一份完整映射每一天的报告，但是我想要的是一份以以下聚合格式生成数据的报告:

PosNo   resID      Date_From   Date_To    seqNo
-------------------------------------------------
10001   NULL       20170101    20171231   0
10001   100        20180101    20180401   1
10001   NULL       20180402    20180530   0
10001   101        20180601    20191231   2
10001   100        20200101    20231231   3


insert into zz_employment 
values ('10001', '100', '2018-01-01 00:00:00.000', '2018-04-01 00:00:00.000', 1),
       ('10001', '101', '2018-06-01 00:00:00.000', '2019-12-31 00:00:00.000', 2),
       ('10001', '100', '2020-01-01 00:00:00.000', '2099-12-31 00:00:00.000', 3)

(请注意报告如何采用表中的两行并生成完整指定的工作生命周期，其中第一个空行日期 from 是从职位开始日期中提取的，最后一行日期 to 是从职位开始日期中提取的职位结束日期。

理想情况下，我希望将其作为 View /函数，但由于其复杂性，我非常乐意拥有一系列 T SQL 语句，我可以将它们作为数据仓库例程的一部分每晚运行。

规则

所有日期都会被截断为日期时间，以便 date_to 引用其结束的日期，而不是其结束的日期和时间
如果职位/雇佣/资源没有结束日期，则它将表示为 20991231
如果就业本身是开放式的，则就业表中的日期将表示为 20991231，即使职位本身可能在 20231231 结束。理想情况下，我希望结果遵循职位结束日期。

SQL 代码:

CREATE TABLE zz_position  
(
     posNo varchar(25) NOT NULL,  
     description varchar(25) NOT NULL,  
     date_from datetime NULL,  
     date_to datetime NULL
) 

insert into zz_position 
values ('10001', 'System Administrator', '2017-01-01 00:00:00.000', '2020-12-31 00:00:00.000')
go

CREATE TABLE zz_resource
(
     resID varchar(25) NOT NULL,  
     description varchar(25) NOT NULL,  
     date_from datetime NULL,  
     date_to datetime NULL
)  

insert into zz_resource 
values ('100', 'Sam', '2016-01-01 00:00:00.000', '2099-12-31 00:00:00.000'),
       ('101', 'Joe', '2015-01-01 00:00:00.000', '2099-12-31 00:00:00.000')
go

CREATE TABLE zz_employment
(
      posNo varchar(25) NOT NULL,  
      resID varchar(25) NOT NULL,  
      date_from datetime NULL,  
      date_to datetime NULL,
      seqNo int NULL
)  

insert into zz_employment 
values ('10001', '100', '2018-01-01 00:00:00.000', '2018-04-01 00:00:00.000', 1),
       ('10001', '101', '2018-06-01 00:00:00.000', '2019-12-31 00:00:00.000', 2),
       ('10001', '100', '2020-01-01 00:00:00.000', '2099-12-31 00:00:00.000', 3)

最佳答案

此问题有两个注意事项:

日历表。
一种在中间有就业期时正确对失业期进行分组的方法。

以下解决方案使用日历表(包括 SQL)和带有 anchor 日期技巧的 DATEDIFF() 来正确分组第二点。

完成DB Fiddle here .

解决方案(解释如下):

;WITH AllPositionDates AS
(
    SELECT
        T.posNo,
        C.GeneratedDate
    FROM
        zz_position AS T
        INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to
),
AllEmployedDates AS
(
    SELECT
        T.posNo,
        T.resID,
        T.seqNo,
        C.GeneratedDate
    FROM
        zz_employment AS T
        INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to
),
PositionsByEmployed AS
(
    SELECT
        P.posNo,
        P.GeneratedDate,
        E.resID,
        E.seqNo,
        NullRowNumber = ROW_NUMBER() OVER (
            PARTITION BY
                P.posNo,
                CASE WHEN E.posNo IS NULL THEN 1 ELSE 2 END
            ORDER BY
                P.GeneratedDate ASC)
    FROM
        AllPositionDates AS P
        LEFT JOIN AllEmployedDates AS E ON
            P.posNo = E.posNo AND
            P.GeneratedDate = E.GeneratedDate
)
SELECT
    P.posNo,
    P.resID,
    Date_From = MIN(P.GeneratedDate),
    Date_To = MAX(P.GeneratedDate),
    seqNo = ISNULL(P.seqNo, 0)
FROM
    PositionsByEmployed AS P
GROUP BY
    P.posNo,
    P.resID,
    P.seqNo,
    CASE WHEN P.resId IS NULL THEN P.NullRowNumber - DATEDIFF(DAY, '2000-01-01', P.GeneratedDate) END -- GroupingValueGroupingValue
ORDER BY
    P.posNo,
    Date_From,
    Date_To

结果:

posNo   resID   Date_From   Date_To     seqNo
10001   NULL    2017-01-01  2017-12-31  0
10001   100     2018-01-01  2018-04-01  1
10001   NULL    2018-04-02  2018-05-31  0
10001   101     2018-06-01  2019-12-31  2
10001   100     2020-01-01  2020-12-31  3

说明

首先创建日历表。每天保存 1 行，在本例中，它仅限于职位的第一天和最后一天:

DECLARE @DateStart DATE = (SELECT MIN(P.date_from) FROM zz_position AS P)
DECLARE @DateEnd DATE = (SELECT(MAX(P.date_to)) FROM zz_position AS P)

;WITH GeneratedDates AS
(
    SELECT
        GeneratedDate = @DateStart

    UNION ALL

    SELECT
        GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate)
    FROM
        GeneratedDates AS G
    WHERE
        DATEADD(DAY, 1, G.GeneratedDate) <= @DateEnd
)
SELECT
    DateID = IDENTITY(INT, 1, 1),
    G.GeneratedDate
INTO
    Calendar
FROM
    GeneratedDates AS G
OPTION
    (MAXRECURSION 0)

这会生成以下内容(截至 2020 年 12 月 31 日，这是示例数据的最大日期):

DateID  GeneratedDate
1       2017-01-01
2       2017-01-02
3       2017-01-03
4       2017-01-04
5       2017-01-05
6       2017-01-06
7       2017-01-07

现在，我们使用中间的连接来“分散”职位的周期和员工的周期(在不同的 CTE 上)，因此我们每天为每个职位/员工获取 1 行。

-- AllPositionDates
SELECT
    T.posNo,
    C.GeneratedDate
FROM
    zz_position AS T
    INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to

-- AllEmployedDates
SELECT
    T.posNo,
    T.resID,
    T.seqNo,
    C.GeneratedDate
FROM
    zz_employment AS T
    INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to

有了这些，我们使用LEFT JOIN按职位和日期将它们连接在一起，这样我们就可以获得每个职位的所有日期和匹配的员工(如果存在)。我们还计算稍后将使用的每个位置的所有 NULL 值的行号。请注意，此行号随着随后的每个日期相应地增加 1 乘 1。

;WITH AllPositionDates AS
(
    SELECT
        T.posNo,
        C.GeneratedDate
    FROM
        zz_position AS T
        INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to
),
AllEmployedDates AS
(
    SELECT
        T.posNo,
        T.resID,
        T.seqNo,
        C.GeneratedDate
    FROM
        zz_employment AS T
        INNER JOIN Calendar AS C ON C.GeneratedDate BETWEEN T.date_from AND T.date_to
)
-- PositionsByEmployee
SELECT
    P.posNo,
    P.GeneratedDate,
    E.resID,
    E.seqNo,
    NullRowNumber = ROW_NUMBER() OVER (
        PARTITION BY
            P.posNo,
            CASE WHEN E.posNo IS NULL THEN 1 ELSE 2 END
        ORDER BY
            P.GeneratedDate ASC)
    FROM
        AllPositionDates AS P
        LEFT JOIN AllEmployedDates AS E ON
            P.posNo = E.posNo AND
            P.GeneratedDate = E.GeneratedDate

现在是棘手的部分。如果我们计算硬编码日期与每一天之间的差异天数，我们会得到一个类似的“行号”，该行号在每个日期中持续增加。

SELECT
    P.posNo,
    P.GeneratedDate,
    DateDiff = DATEDIFF(DAY, '2000-01-01', P.GeneratedDate),
    P.NullRowNumber
FROM
    PositionsByEmployed AS P -- This is declare with the WITH (full solution below)
ORDER BY
    P.posNo,
    P.GeneratedDate

我们得到以下信息:

posNo   GeneratedDate   DateDiff    NullRowNumber
10001   2017-01-01      6210        1
10001   2017-01-02      6211        2
10001   2017-01-03      6212        3
10001   2017-01-04      6213        4
10001   2017-01-05      6214        5
10001   2017-01-06      6215        6
10001   2017-01-07      6216        7
10001   2017-01-08      6217        8
10001   2017-01-09      6218        9

如果我们添加另一列与其余两列，您将看到该值保持不变:

SELECT
    P.posNo,
    P.GeneratedDate,
    DateDiff = DATEDIFF(DAY, '2000-01-01', P.GeneratedDate),
    P.NullRowNumber,
    GroupingValue = P.NullRowNumber - DATEDIFF(DAY, '2000-01-01', P.GeneratedDate)
FROM
    PositionsByEmployed AS P
ORDER BY
    P.posNo,
    P.GeneratedDate

我们得到:

posNo   GeneratedDate   DateDiff    NullRowNumber   GroupingValue
10001   2017-01-01      6210        1               -6209
10001   2017-01-02      6211        2               -6209
10001   2017-01-03      6212        3               -6209
10001   2017-01-04      6213        4               -6209
10001   2017-01-05      6214        5               -6209
10001   2017-01-06      6215        6               -6209
10001   2017-01-07      6216        7               -6209
10001   2017-01-08      6217        8               -6209
10001   2017-01-09      6218        9               -6209
10001   2017-01-10      6219        10              -6209

但是，如果我们向下滚动直到看到员工的 NULL 值(来自 ROW_NUMBER() PARTITION BY 表达式 E.PosNo )，我们就会看到其余的值有所不同，因为 ROW_NUMBER() 一直以 1 递增，而 DATEDIFF 则跳跃，因为中间有就业人员:

posNo   GeneratedDate   DateDiff    NullRowNumber   GroupingValue
10001   2017-12-28      6571        362             -6209
10001   2017-12-29      6572        363             -6209
10001   2017-12-30      6573        364             -6209
10001   2017-12-31      6574        365             -6209
...
10001   2018-04-02      6666        366             -6300
10001   2018-04-03      6667        367             -6300
10001   2018-04-04      6668        368             -6300
10001   2018-04-05      6669        369             -6300
10001   2018-04-06      6670        370             -6300
10001   2018-04-07      6671        371             -6300

使用此“GroupingValue”作为附加的GROUP BY来正确分隔超出所使用间隔的位置间隔。

关于sql-server - SQL Server : finding gaps in employment - island and gap problem，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/54771013/

文章推荐： scala - Scala 中难以理解的语法

文章推荐： jsf - JSF h 的 PrimeFaces 标签 :inputHidden

文章推荐： qt - Qt 对象的名称必须是唯一的吗？

java - 的 TreeSet 列表对列表进行排序并显示列表中具有特定属性的人员
我有一个 Employee 列表(Employee 是一个类)。员工有 3 个属性 private String name private int yearsSpentInCompany pri
language-agnostic - 学生项目 : do they influence employment prospects?
关闭。这个问题是opinion-based .它目前不接受答案。想改善这个问题吗？更新问题，以便可以通过 editing this post 用事实和引文回答问题. 8年前关闭。 Improve t
c++ - 两阶段函数模板编译 : not *only* ADL is employed in the 2nd phase?
我想知道为什么 following code编译。 #include template void print(T t) { std::cout void print(T t) {
java - 轴2 : employ inheritance for codegenerated request/response objects
这个问题非常简单，所以我希望我能足够清楚，以避免为其创建代码示例。我们正在使用 Axis2(准确地说是 1.5.1)，它使用我们手工制作的 WSDL/XSD 文件通过 wsdl2java 创建 Jav
mysql - 如何在 customer 下创建一个引用 employer 表中的 "id"的列
CREATE TABLE College ( CollegeID INTEGER AUTO_INCREMENT NOT NULL, Collegename varchar(50),
python - 使用 SQLAlchemy 创建非持久对象 : wrong employment of backref in a relationship?
我想创建给定类 Task 的对象，使用 SQLAlchemy 将其中一些对象存储在数据库中，并丢弃其他对象。目前这个类是用以下内容创建的: Base = declarative_base() clas
sql-server - SQL Server : finding gaps in employment - island and gap problem
上周我一直在尝试解决堆栈溢出问题，但我仍然无法找到可行的解决方案，所以想知道是否有人可以为我提供一些帮助/建议？数据结构说明我有下表: 位置表(zz_position)，用于保存详细信息职位(职位

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql-server - SQL Server : finding gaps in employment - island and gap problem

解决方案(解释如下):

说明