Summarizing values where a summed column is exceeded within a date range

user_15826708 0
Viewed 47

I have a table that contains position data relating to an employee. An employee may have more than one active position and the split can be defined by setting the percentage that applies. Positions have a start date and a end date. The default end date is 2099 which means the end hasn't been specified (on-going).

The data is entered through an ERP system and it's possible to exceed 100% during a given date range.

I want to build a query to summarize how many employees there are in each company that have position percentage split that exceed 100%. Only for active employee's though.

I have put together some sample data in Fiddle to illustrate this - http://sqlfiddle.com/#!18/0e36d9

EDIT: Here is some sample data:

status company EmpId PositionCode PositionPerc Date_From Date_To
ACTIVE EN 1234 001 100 2012-06-12T00:00:00Z 2021-01-12T00:00:00Z
ACTIVE EN 1234 002 100 2021-01-13T00:00:00Z 2099-12-31T00:00:00Z
ACTIVE EN 4321 001 80 2019-06-17T00:00:00Z 2099-12-31T00:00:00Z
ACTIVE EN 4321 003 30 2021-03-10T00:00:00Z 2099-12-31T00:00:00Z
ACTIVE EN 7811 002 100 2019-06-17T00:00:00Z 2020-11-12T00:00:00Z
ACTIVE NO 2234 002 50 2019-06-17T00:00:00Z 2099-12-31T00:00:00Z
ACTIVE NO 2234 005 50 2020-02-21T00:00:00Z 2099-12-31T00:00:00Z
ACTIVE GB 4121 001 100 2021-01-17T00:00:00Z 2099-12-31T00:00:00Z
ACTIVE GB 4121 006 100 2021-04-17T00:00:00Z 2099-12-31T00:00:00Z
ACTIVE EN 5551 001 50 2015-06-17T00:00:00Z 2099-12-31T00:00:00Z
ACTIVE EN 5551 003 60 2021-03-10T00:00:00Z 2099-12-31T00:00:00Z

I'm trying to get the data back like this:

Company Emps_that_exceed
EN 2
NO 0
GB 1

I would really appreciate any help. I've tried to search on this but I'm struggling to describe it accurately enough to get anything useful back.

I know it's going to be something along the lines of

select client, SUM(select....) from positiondata group by client

I've not had to do a query like this before.

tsql sql-server sql
1 Answers

The idea is to unpivot the data so you can do a cumulative sum. On the day after the "to" date, you subtract the positionperc amount.

Then, just summarize by company to get the total employees who are in excess:

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;

Here is a db<>fiddle.

user_11440350
Related