SQL Server - Query to split time by count (overlapping offices)

Question

I'm looking for some advice on the approach I should take with a query. I have a table (EMP) which stores employee details and working hours for this year (40 hours per week). A further 2 tables store the primary and secondary offices employees belong to. Since employees can move between offices, these are stored with dates.

I'm looking to return the number of working hours during the time the employee is in an office. If primary offices overlap with secondary offices for an employee, the hours should be split by the number of overlapping offices for the overlapping period only.

I attach sample DDL below.

-- Employee Table with hours for year 2014

CREATE TABLE [dbo].[EMP](
    [EMP_ID] [int] NOT NULL,
    [EMP_NAME] [varchar](255) NULL,
    [EMP_FYHOURS] [float] NULL,
 CONSTRAINT [PK_EMP] PRIMARY KEY CLUSTERED 
(
    [EMP_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Employees and their primary offices

CREATE TABLE [dbo].[OFFICEPRIMARY](
    [OFFICEPRIMARY_ID] [int] NOT NULL,
    [OFFICEPRIMARY_NAME] [varchar](255) NULL,
    [OFFICEPRIMARY_EMP_ID] [int] NOT NULL,
    [OFFICEPRIMARY_START] [datetime] NULL,
    [OFFICEPRIMARY_END] [datetime] NULL,
 CONSTRAINT [PK_OFFICEPRIMARY] PRIMARY KEY CLUSTERED 
(
    [OFFICEPRIMARY_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[OFFICEPRIMARY]  WITH CHECK ADD  CONSTRAINT [FK_OFFICEPRIMARY_FK1] FOREIGN KEY([OFFICEPRIMARY_EMP_ID])
REFERENCES [dbo].[EMP] ([EMP_ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[OFFICEPRIMARY] CHECK CONSTRAINT [FK_OFFICEPRIMARY_FK1]
GO

-- Employees and their secondary offices

CREATE TABLE [dbo].[OFFICESECONDARY](
    [OFFICESECONDARY_ID] [int] NOT NULL,
    [OFFICESECONDARY_NAME] [varchar](255) NULL,
    [OFFICESECONDARY_EMP_ID] [int] NOT NULL,
    [OFFICESECONDARY_START] [datetime] NULL,
    [OFFICESECONDARY_END] [datetime] NULL,
 CONSTRAINT [PK_OFFICESECONDARY] PRIMARY KEY CLUSTERED 
(
    [OFFICESECONDARY_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[OFFICESECONDARY]  WITH CHECK ADD  CONSTRAINT [FK_OFFICESECONDARY_FK1] FOREIGN KEY([OFFICESECONDARY_EMP_ID])
REFERENCES [dbo].[EMP] ([EMP_ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[OFFICESECONDARY] CHECK CONSTRAINT [FK_OFFICESECONDARY_FK1]
GO

-- Insert sample data

INSERT INTO EMP (EMP_ID, EMP_NAME, EMP_FYHOURS)
VALUES (1, 'John Smith', 2080);

INSERT INTO EMP (EMP_ID, EMP_NAME, EMP_FYHOURS)
VALUES (2, 'Jane Doe', 2080);

GO

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (1, 'London', 1, '2014-01-01', '2014-05-31')

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (2, 'Berlin', 1, '2014-06-01', '2014-08-31')

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (3, 'New York', 1, '2014-09-01', '2014-12-31')

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (4, 'New York', 2, '2014-01-01', '2014-04-15')

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (5, 'Paris', 2, '2014-04-16', '2014-09-30')

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (6, 'London', 2, '2014-10-01', '2014-12-31')

GO

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (1, 'Paris', 1, '2014-01-01', '2014-03-31')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (2, 'Lyon', 1, '2014-04-01', '2014-05-15')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (3, 'Berlin', 1, '2014-05-16', '2014-09-30')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (4, 'Chicago', 1, '2014-10-01', '2015-02-22')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (5, 'Chicago', 2, '2013-11-21', '2014-04-10')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (6, 'Berlin', 2, '2014-04-11', '2014-09-16')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (7, 'Amsterdam', 2, '2014-09-17', '2015-03-31')

GO

Thanks for the pointer. I adjusted your query so it presents a union of the primary and secondary office.

All that remains is working out the hours for overlapping periods between offices. For example,

John Smith, New York, 01/04/2014, 10/08/2014

John Smith, London, 01/08/2014, 31/12/2014

For the overlapping period between the offices which is 01/08/2014 to 10/08/2014, I would expect the hours to be split equally. If there were 3 overlapping offices, then it would be split 3-ways.

select 'Primary' as Office, e.EMP_NAME, op.OFFICEPRIMARY_NAME, op.OFFICEPRIMARY_START, op.OFFICEPRIMARY_END, datediff(wk,OFFICEPRIMARY_START,OFFICEPRIMARY_END) * 40 as HoursWorkedPrimary
from EMP e
inner join OFFICEPRIMARY op on op.OFFICEPRIMARY_EMP_ID = e.EMP_ID
union all
select 'Secondary' as Office, e.EMP_NAME, os.OFFICESECONDARY_NAME, os.OFFICESECONDARY_START, os.OFFICESECONDARY_END, datediff(wk,OFFICESECONDARY_START,OFFICESECONDARY_END) * 40 as HoursWorkedSecondary
from EMP e
inner join OFFICESECONDARY os on os.OFFICESECONDARY_EMP_ID = e.EMP_ID
order by e.EMP_NAME

score 0 · Answer 1 · answered Jul 09 '14 at 09:28

This should give you a head start:

select datediff(wk,OFFICEPRIMARY_START,OFFICEPRIMARY_END) * 40 as HoursWorkedPrimary
,datediff(wk,OFFICESECONDARY_START,OFFICESECONDARY_END) * 40 as HoursWorkedSecondary
,EMP_NAME
,OFFICEPRIMARY_NAME,OFFICEPRIMARY_START,OFFICEPRIMARY_END
,OFFICESECONDARY_NAME,OFFICESECONDARY_START,OFFICESECONDARY_END
from [EMP]
inner join OFFICEPRIMARY as op on op.OFFICEPRIMARY_EMP_ID = EMP.EMP_ID
inner join OFFICESECONDARY as os on os.OFFICESECONDARY_EMP_ID = EMP.EMP_ID

Thanks for the pointer. I adjusted your query so it presents a union of the primary and secondary office. — Confounder, Jul 09 '14 at 09:59

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

The link below should help point you in the right direction to identifying how the dates overlap.

Count days in date range with set of exclusions which may overlap

edited May 23 '17 at 12:22

Community

1
1

answered Jul 09 '14 at 10:00

user3671364

16
1

saso · Accepted Answer · 2014-07-09T10:21:52.877

If I understand correctly, the end result you want to see is the number of total hours worked per employee and office?

I've come up with this:

-- generate date table
declare @MinDate datetime, @MaxDate datetime
SET @MinDate = (SELECT MIN(d) FROM (SELECT d = OFFICEPRIMARY_START FROM dbo.OFFICEPRIMARY UNION SELECT OFFICESECONDARY_START FROM dbo.OFFICESECONDARY) a)
SET @MaxDate = (SELECT MAX(d) FROM (SELECT d = OFFICEPRIMARY_END FROM dbo.OFFICEPRIMARY UNION SELECT OFFICESECONDARY_END FROM dbo.OFFICESECONDARY) a)

SELECT
    d = DATEADD(day, number, @MinDate)
INTO
    #tmp_dates
FROM 
    (SELECT DISTINCT number FROM master.dbo.spt_values WHERE name IS NULL) n
WHERE
    DATEADD(day, number, @MinDate) < @MaxDate


;WITH CTE AS
(
SELECT  
    d.d
    ,o.OfficeType
    ,o.OfficeID
    ,o.OfficeName
    ,o.EmpID
    ,EmpName = e.EMP_NAME
    ,HoursWorked = 8 / (COUNT(1) OVER (PARTITION BY EmpID, d))
FROM
    (
        SELECT
            OfficeType = 1
            ,OfficeID = op.OFFICEPRIMARY_ID
            ,OfficeName = op.OFFICEPRIMARY_NAME
            ,EmpID = op.OFFICEPRIMARY_EMP_ID
            ,StartDate = op.OFFICEPRIMARY_START
            ,EndDate = op.OFFICEPRIMARY_END
        FROM 
            dbo.OFFICEPRIMARY op

        UNION

        SELECT
            OfficeType = 2
            ,OfficeID = os.OFFICESECONDARY_ID
            ,OfficeName = os.OFFICESECONDARY_NAME
            ,EmpID = os.OFFICESECONDARY_EMP_ID
            ,StartDate = os.OFFICESECONDARY_START
            ,EndDate = os.OFFICESECONDARY_END
        FROM 
            dbo.OFFICESECONDARY os
    ) o

INNER JOIN
    dbo.EMP e ON e.EMP_ID = o.EmpID
INNER JOIN
    #tmp_dates d ON o.StartDate<=d.d AND o.EndDate>=d.d
)

SELECT
    EmpID
    ,EmpName
    ,OfficeType
    ,OfficeName
    ,TotalHoursWorked = SUM(HoursWorked)
FROM
    CTE
GROUP BY
    EmpID
    ,EmpName
    ,OfficeType
    ,OfficeID
    ,OfficeName
ORDER BY
    EmpID
    ,OfficeName

I first generate a temp table with the dates between minimum date and maximum date.

Then I union both office tables (why you have 2 tables anyway?) and I get a CTE that returns data on employee, date, office and number of hours worked in this office (8 divided by count of offices where employee has worked in on this day).

Then I sum this data to get sum of hours grouped by employee and office.

Maybe there is a simpler solution to this. This was the first solution that came to my mind.

SQL Server - Query to split time by count (overlapping offices)

3 Answers3