Início / dba / Perguntas / 339492
Accepted
Stefan
Asked: 2024-05-14 19:02:52 +0800 CST

Dividir intervalos com duas tabelas

  • 772

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.

sql-server

1 respostas

  1. Best Answer

    Esta solução é baseada em uma tabela de calendário. Para isso adicionei este trecho de código (se tiver em seu sistema, basta substituí-lo e usar o seu)

    (SELECT DISTINCT DATEADD(DAY,v.number,'2020-10-01') as sptData
            FROM master.dbo.spt_values as v 
                    CROSS JOIN master.dbo.spt_values as v2
            WHERE v.type = 'P'
                and v2.type ='P'
                AND v2.number < 10
             ) as d /*this creates a list of days*/

    Além disso, isso pode ser substituído por:

    SELECT DISTINCT src.ValidFrom as sptData
FROM
    (
        SELECT m.ValidFrom FROM CCmain as m GROUP BY m.ValidFrom
        UNION ALL
        SELECT DATEADD(dAY,1,m.ValidTo) FROM CCmain as m GROUP BY m.ValidTo
        UNION ALL

        SELECT f.ValidFrom FROM CCfraction as f GROUP BY f.ValidFrom
        UNION ALL
        SELECT DATEADD(DAY,1,f.ValidTo) FROM CCfraction as f GROUP BY f.ValidTo
    )as src

    Depois concatenei todas as informações em uma única coluna, uma para tabela principal e outra para fração

    ,CONCAT_WS(';',m.PN,m.ValidFrom,m.ValidTo,m.Costcenter, f.PNF,f.ValidFromF,f.ValidToF) as txt
,CONCAT_WS(';',m.PN,m.ValidFrom,m.ValidTo,f.CostCenterF,f.Fraction) as txt2

    O próximo passo é obter o campo anterior para txt e txt2

    LAG(txt,1,NULL) OVER(PARTITION BY d.PN ORDER BY d.sptData , d.txt  ASC) as prev_txt,
LAG(txt2,1,NULL) OVER(PARTITION BY d.PN ORDER BY d.txt2,d.sptData  ASC) as prev_txt2

    E então verifique se um novo intervalo é iniciado

    CASE WHEN ISNULL(d.txt,'') <> ISNULL(d.prev_txt,'') THEN 1 
          WHEN ISNULL(d.txt2,'') <> ISNULL(d.prev_txt2,'') THEN 1 
          ELSE 0 
     END as grp

    a seleção final:

    SELECT *
    ,CASE WHEN ISNULL(d.txt,'') <> ISNULL(d.prev_txt,'') THEN 1 
          WHEN ISNULL(d.txt2,'') <> ISNULL(d.prev_txt2,'') THEN 1 
          ELSE 0 
     END as grp
     ,grpSum = SUM(
                    CASE WHEN ISNULL(d.txt,'') <> ISNULL(d.prev_txt,'') THEN 1 
                          WHEN ISNULL(d.txt2,'') <> ISNULL(d.prev_txt2,'') THEN 1 
                          ELSE 0 
                    END) OVER(PARTITION BY d.PN ORDER BY d.sptData /*ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT RANGE*/)
INTO #tmp
FROM
(
    SELECT
        d.*,
        LAG(txt,1,NULL) OVER(PARTITION BY d.PN ORDER BY d.sptData , d.txt  ASC) as prev_txt,
        LAG(txt2,1,NULL) OVER(PARTITION BY d.PN ORDER BY d.txt2,d.sptData  ASC) as prev_txt2
    FROM
    (
        SELECT [sptData] ,
            m.*,
            f.*
            ,CONCAT_WS(';',m.PN,m.ValidFrom,m.ValidTo,m.Costcenter, f.PNF,f.ValidFromF,f.ValidToF/*,f.CostCenterF,f.Fraction*/) as txt
            ,CONCAT_WS(';',m.PN,m.ValidFrom,m.ValidTo,f.CostCenterF,f.Fraction) as txt2
        FROM (SELECT DISTINCT DATEADD(DAY,v.number,'2020-10-01') as sptData
            FROM master.dbo.spt_values as v 
                    CROSS JOIN master.dbo.spt_values as v2
            WHERE v.type = 'P'
                and v2.type ='P'
                AND v2.number < 10
             ) as d
            CROSS JOIN
            (
                SELECT DISTINCT m.PN
                FROM CCmain as m
            )as P
            OUTER APPLY
            (
                SELECT m.PN,m.ValidFrom,m.ValidTo,m.Costcenter
                FROM CCmain as m
                WHERE d.sptData>=m.ValidFrom
                    AND d.sptData<=m.ValidTo
                    AND m.PN = p.PN
            )as m
            OUTER APPLY
            (
                SELECT f.ValidFrom as ValidFromF, f.ValidTo as ValidToF, f.PN as PNF,f.Costcenter as CostCenterF, f.Fraction
                FROM CCfraction as f
                WHERE d.sptData>=f.ValidFrom
                    AND d.sptData<=f.ValidTo
                    AND f.PN = p.PN
            )f
            
        WHERE 
            sptData >= '2020-10-01' 
            AND sptData <='2023-12-31'
    )as d
)as d
ORDER BY
    d.sptData ASC



SELECT  --DISTINCT
    t.PN,
    ISNULL(t.CostCenterF, t.CostCenter) as CostCenter,
    t.Fraction as Fraction,
    CAST(t.sptData as date) as Data1,   
    CASE WHEN t.ValidTo < t.ValidToF THEN t.ValidTo 
         WHEN t.ValidToF <= t.ValidTo THEN t.ValidToF
         ELSE oa.sptData
    END as Data2
    /*,
    t.PN,t.ValidFrom,  t.ValidTo,t.Costcenter,
    t.ValidFromF, t.ValidToF , t.CostCenterF,t.Fraction,
    grp,grpSum*/
FROM 
    #tmp as t   
    OUTER APPLY
    (
        SELECT TOP(1) CAST(DATEADD(DAy,-1,sptData) as date) as sptData
        FROM #tmp as t2
        WHERE t2.PN = t.PN
            AND grp = 1
            AND t2.sptData > t.sptData
        ORDER BY t2.sptData ASC
    )OA
WHERE grp = 1
    and t.ValidFrom IS NOT NULL
ORDER BY Data1,Data2 ASC

    saída:

    PN Centro de custo Fração Dados1 Dados2
    1 100 nulo 01/10/2020 31-12-2020
    1 200 nulo 2021-01-01 30/04/2021
    1 300 40 01/05/2021 31/07/2021
    1 200 60 01/05/2021 31/07/2021
    1 200 nulo 01/08/2021 31-12-2021
    1 100 70 01/01/2022 30/06/2022
    1 300 30 01/01/2022 30/06/2022
    1 100 55 01/07/2022 30/09/2022
    1 200 45 01/07/2022 30/09/2022
    1 200 45 01/01/2023 31/03/2023
    1 100 55 01/01/2023 31/03/2023

    dbfiddle aqui

    • 1

relate perguntas