Tenho duas tabelas que alocam funcionários para centros de custo. A primeira tabela ( CCmain) mostra o centro de custo primário. Se um funcionário estiver atribuído a mais de um centro de custo, a segunda tabela ( CCfraction) mostra qual fração do funcionário está atribuída a qual centro de custo.
Para uma avaliação de BI, preciso dividir em qual período o funcionário está em qual centro de custo.
Que consulta dará esse resultado?
Isso ilustra uma situação:
Time
CCmain: <---><-------------><----------> <--->
CCfraction: <---> <----><--------------->
Result: <---><---><---><---><----><----> <--->
Dados de amostra
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
Saída desejada
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
De 01/10/2022 até 31/12/2022 não há CC, embora haja uma fração.
O caso é semelhante a este , mas vai um pouco além quando uma alocação de centro de custo ocorre em um período de tempo maior.
Formação técnica
Conforme solicitado, um pouco mais de histórico.
Para explicar o contexto com mais detalhes. As tabelas vêm de um software de gestão de pessoal. Na verdade, existem ainda mais tabelas envolvidas neste problema, mas eu queria reduzir o exemplo ao mínimo.
Cada funcionário é atribuído a pelo menos um centro de custo. Isso deve ser representado na tabela CCmain. Existem funcionários alocados em vários centros de custo. A alocação do empregado é então inserida na tabela CCfraction. Existem então pelo menos duas entradas na tabela CCfraction. Um para o centro de custo principal e outro para o outro centro de custo ao qual o funcionário está alocado. Também é possível que um funcionário seja alocado em três ou mais centros de custo. Nesse caso, haveria mais entradas.
O que removi na redução deste exemplo é que existe outra tabela entre as tabelas CCmain e CCfraction. Esta tabela permite que vários empregados utilizem a mesma divisão de centros de custo. Assim como neste exemplo, é possível que um funcionário não possua centro de custo mestre, mas a alocação continue rodando porque pode ser válida para outros funcionários. Esta é a base técnica. Desculpe por ter reduzido o exemplo ao mínimo.