Split intervals with two tables

Question

I have two tables that allocate employees to cost centers. The first table (CCmain) shows the primary cost center. If an employee is assigned to more than one cost center, the second table (CCfraction) shows what fraction of the employee is assigned to which cost center.

For a BI evaluation, I need the splitting in witch timespan an employee is in which cost center.

What query will give this result?

This illustrates one situation:

Time
CCmain:     <---><-------------><---------->   <--->
CCfraction:           <--->     <----><--------------->

Result:     <---><---><---><---><----><---->   <--->

Sample Data

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

Desired output

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

From 2022-10-01 until 2022-12-31 is no CC, although there is a fraction.

The case is similar to this one, but it goes a little further when a cost center allocation is within a longer period of time.

Technical background

As requested, a bit more background.

To explain the background in more detail. The tables come from a personnel management software. There are actually even more tables involved in this problem, but I wanted to reduce the example to a minimum.

Each employee is assigned to at least one cost center. This should be represented with the CCmain table. There are employees who are assigned to several cost centers. The allocation of the employee is then entered in the CCfraction table. There are then at least two entries in the CCfraction table. One for the main cost center and one for the other cost center to which the employee is allocated. It is also possible for an employee to be allocated to three or more cost centers. In this case, there would be more entries.

What I have removed in the reduction to this example is that there is another table between the CCmain and CCfraction tables. This table allows several employees to use the same cost center splitting. As in this example, it is possible that an employee does not have a master cost center, but the allocation continues to run because it can be valid for other employees. This is the technical background. Sorry that I have reduced the example to a minimum.

Answer 1

This solution is base on a calendar table . For this I added this piece of code(if you have in your system, just replace it and use yours)

(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*/

Also, this could be replace with:

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

Then I concatenated all the information into a single column , one for main table, and one for fraction

,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

Next step, is to get the previous field for txt and 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

And then check then a new interval is started

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

the final select :

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

output:

PN	CostCenter	Fraction	Data1	Data2
1	100	null	2020-10-01	2020-12-31
1	200	null	2021-01-01	2021-04-30
1	300	40	2021-05-01	2021-07-31
1	200	60	2021-05-01	2021-07-31
1	200	null	2021-08-01	2021-12-31
1	100	70	2022-01-01	2022-06-30
1	300	30	2022-01-01	2022-06-30
1	100	55	2022-07-01	2022-09-30
1	200	45	2022-07-01	2022-09-30
1	200	45	2023-01-01	2023-03-31
1	100	55	2023-01-01	2023-03-31

dbfiddle here