# Split intervals with two tables

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.

• Why the timerange from 2022-09-30 till 2023-01-01 is lost? last two rows of CCfraction covers it.. Commented May 14 at 13:41
• can you please explain the logic better, it is very unclear how you got your end result
– nbk
Commented May 14 at 14:48

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

• That's an interesting approach. And the best thing is that it works. Thanks for the idea. Commented May 24 at 10:07
• Your are welcome! Commented May 24 at 10:14