SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE positiondata 
    ([status] varchar(6), [company] varchar(2), [EmpId] varchar(4), [PositionCode] varchar(3), [PositionPerc] decimal(5, 2),   [Date_From] datetime, [Date_To] datetime)
;
    
INSERT INTO positiondata 
    ([status], [company], [EmpId], [PositionCode], [PositionPerc], [Date_From], [Date_To])
VALUES
    ('ACTIVE', 'EN', '1234', '001', 100.00, '2012-06-12 00:00:00', '2021-01-12 00:00:00'),
    ('ACTIVE', 'EN', '1234', '002', 100.00, '2021-01-13 00:00:00', '2099-12-31 00:00:00'),
    ('ACTIVE', 'EN', '4321', '001', 80.00, '2019-06-17 00:00:00', '2099-12-31 00:00:00'),
    ('ACTIVE', 'EN', '4321', '003', 30.00, '2021-03-10 00:00:00', '2099-12-31 00:00:00'),  
    ('ACTIVE', 'EN', '7811', '002', 100.00, '2019-06-17 00:00:00', '2020-11-12 00:00:00'),
    ('ACTIVE', 'NO', '2234', '002', 50.00, '2019-06-17 00:00:00', '2099-12-31 00:00:00'),  
    ('ACTIVE', 'NO', '2234', '005', 50.00, '2020-02-21 00:00:00', '2099-12-31 00:00:00'),  
    ('ACTIVE', 'GB', '4121', '001', 100.00, '2021-01-17 00:00:00', '2099-12-31 00:00:00'),  
    ('ACTIVE', 'GB', '4121', '006', 100.00, '2021-04-17 00:00:00', '2099-12-31 00:00:00'),
    ('ACTIVE', 'EN', '5551', '001', 50.00, '2015-06-17 00:00:00', '2099-12-31 00:00:00'),
    ('ACTIVE', 'EN', '5551', '003', 60.00, '2021-03-10 00:00:00', '2099-12-31 00:00:00')

;

-- The total PositionPerc cannot exceed 100 with overlapping dates.
-- EmpID 1234 is fine because the percentage doesnt exceed 100 within the same date range.
-- EmpID 4321 in this case is a problem as positionCode 001 and 003 will overlap with a PositionPerc total of 120.00
-- EmpId 4121 is also a problem
11 rows affected

select *
from positiondata


status
company
EmpId
PositionCode
PositionPerc
Date_From
Date_To


ACTIVE
EN
1234
001
100.00
2012-06-12 00:00:00.000
2021-01-12 00:00:00.000

ACTIVE
EN
1234
002
100.00
2021-01-13 00:00:00.000
2099-12-31 00:00:00.000

ACTIVE
EN
4321
001
80.00
2019-06-17 00:00:00.000
2099-12-31 00:00:00.000

ACTIVE
EN
4321
003
30.00
2021-03-10 00:00:00.000
2099-12-31 00:00:00.000

ACTIVE
EN
7811
002
100.00
2019-06-17 00:00:00.000
2020-11-12 00:00:00.000

ACTIVE
NO
2234
002
50.00
2019-06-17 00:00:00.000
2099-12-31 00:00:00.000

ACTIVE
NO
2234
005
50.00
2020-02-21 00:00:00.000
2099-12-31 00:00:00.000

ACTIVE
GB
4121
001
100.00
2021-01-17 00:00:00.000
2099-12-31 00:00:00.000

ACTIVE
GB
4121
006
100.00
2021-04-17 00:00:00.000
2099-12-31 00:00:00.000

ACTIVE
EN
5551
001
50.00
2015-06-17 00:00:00.000
2099-12-31 00:00:00.000

ACTIVE
EN
5551
003
60.00
2021-03-10 00:00:00.000
2099-12-31 00:00:00.000


with ced as (
      select pd.company, pd.empid, v.date, sum(v.positionperc) as positionperc,
             sum(sum(v.positionperc)) over (partition by pd.company, pd.empid order by v.date) as total_positionperc
      from positiondata pd cross apply
           (values (pd.date_from, pd.positionperc),
                    (dateadd(day, 1, pd.date_to), - pd.positionperc)
           ) v(date, positionperc)
      group by pd.company, pd.empid, date
     )
select company,
       count(distinct case when total_positionperc > 100 then empid end)
from ced
group by company


company
(No column name)


EN
2

GB
1

NO
0


Warning: Null value is eliminated by an aggregate or other SET operation.

status	company	EmpId	PositionCode	PositionPerc	Date_From	Date_To
ACTIVE	EN	1234	001	100.00	2012-06-12 00:00:00.000	2021-01-12 00:00:00.000
ACTIVE	EN	1234	002	100.00	2021-01-13 00:00:00.000	2099-12-31 00:00:00.000
ACTIVE	EN	4321	001	80.00	2019-06-17 00:00:00.000	2099-12-31 00:00:00.000
ACTIVE	EN	4321	003	30.00	2021-03-10 00:00:00.000	2099-12-31 00:00:00.000
ACTIVE	EN	7811	002	100.00	2019-06-17 00:00:00.000	2020-11-12 00:00:00.000
ACTIVE	NO	2234	002	50.00	2019-06-17 00:00:00.000	2099-12-31 00:00:00.000
ACTIVE	NO	2234	005	50.00	2020-02-21 00:00:00.000	2099-12-31 00:00:00.000
ACTIVE	GB	4121	001	100.00	2021-01-17 00:00:00.000	2099-12-31 00:00:00.000
ACTIVE	GB	4121	006	100.00	2021-04-17 00:00:00.000	2099-12-31 00:00:00.000
ACTIVE	EN	5551	001	50.00	2015-06-17 00:00:00.000	2099-12-31 00:00:00.000
ACTIVE	EN	5551	003	60.00	2021-03-10 00:00:00.000	2099-12-31 00:00:00.000