我有两个表将员工分配到成本中心。第一个表 ( CCmain
) 显示主要成本中心。如果一名员工被分配到多个成本中心,第二个表 ( CCfraction
) 显示该员工的比例被分配到哪个成本中心。
对于 BI 评估,我需要划分员工在哪个成本中心的时间跨度。
什么查询会给出这个结果?
这说明了一种情况:
Time
CCmain: <---><-------------><----------> <--->
CCfraction: <---> <----><--------------->
Result: <---><---><---><---><----><----> <--->
样本数据
CREATE TABLE CCmain(
ValidFrom date NOT NULL,
ValidTo date NOT NULL,
Costcenter int NOT NULL,
PN int NULL
);
CREATE TABLE CCfraction(
PN int NOT NULL,
ValidFrom date NOT NULL,
ValidTo date NOT NULL,
Costcenter int NOT NULL,
Fraction int NOT NULL
);
GO
INSERT INTO CCmain (ValidFrom,ValidTo,Costcenter,PN) VALUES
('2020-10-01','2020-12-31',100,1),
('2021-01-01','2021-12-31',200,1),
('2022-01-01','2022-09-30',100,1),
('2023-01-01','2023-03-31',300,1);
INSERT INTO CCfraction (PN,ValidFrom,ValidTo,Costcenter,Fraction) VALUES
(1,'2021-05-01','2021-07-31',200,60),
(1,'2021-05-01','2021-07-31',300,40),
(1,'2022-01-01','2022-06-30',100,70),
(1,'2022-01-01','2022-06-30',300,30),
(1,'2022-07-01','2023-12-31',100,55),
(1,'2022-07-01','2023-12-31',200,45);
GO
所需输出
PN Costcenter Fraction ValidFrom ValidTo
-- ---------- -------- ---------- ----------
1 100 100 2020-10-01 2020-12-31 CC 100 no split
1 200 100 2021-01-01 2021-04-30 CC 200 no split
1 300 40 2021-05-01 2021-07-31 CC 300 40% and
1 200 60 2021-05-01 2021-07-31 60% CC 200
1 200 100 2021-08-01 2021-12-31 CC 200 no split
1 100 70 2022-01-01 2022-06-30 CC 100 70% and
1 300 30 2022-01-01 2022-06-30 30% CC 300
1 100 55 2022-07-01 2022-09-30 CC 100 55% and
1 200 45 2022-07-01 2022-09-30 45% CC 200
1 100 55 2023-01-01 2023-03-31 CC 100 55% and
1 200 45 2023-01-01 2023-03-31 45% CC 200
从 2022-10-01 到 2022-12-31 没有 CC,尽管有一小部分。
这种情况与此类似,但当成本中心分配处于较长时间段内时,情况会更进一步。
技术背景
根据要求,多一点背景知识。
更详细地解释背景。这些表格来自人事管理软件。实际上这个问题涉及更多的表,但我想将示例减少到最少。
每位员工至少被分配到一个成本中心。这应该用 CCmain 表来表示。有些员工被分配到多个成本中心。然后将员工的分配输入到 CCfraction 表中。CCfraction 表中至少有两个条目。一个用于主成本中心,另一个用于分配员工的另一个成本中心。也可以将一名员工分配到三个或更多成本中心。在这种情况下,会有更多条目。
我在这个示例的简化中删除的是 CCmain 和 CCfraction 表之间还有另一个表。该表允许多个员工使用相同的成本中心拆分。如此例中,员工可能没有主成本中心,但分配会继续运行,因为它可能对其他员工有效。这就是技术背景。抱歉,我已将示例减少到最少。
该解决方案基于日历表。为此,我添加了这段代码(如果您的系统中有,只需替换它并使用您的)
另外,这可以替换为:
然后我将所有信息连接到一列中,一列用于主表,一列用于分数
下一步是获取 txt 和 txt2 的前一个字段
然后检查是否开始新的间隔
最终选择:
输出:
dbfiddle在这里