SQL Divide time by overlapping date ranges

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Divide time by overlapping date ranges

Author

Topic

hanifbarik
Starting Member

9 Posts

Posted - 2014-09-08 : 09:20:35

I've been struggling with a query on and off for a few weeks now. In it's simplest form, I am looking to divide time if there is an overlap between groups. If there are 100 hours each month and there is an overlap between groups, it should divide that time by 2 for the overlapping period only regardless of how many groups overlap, otherwise, return 100 for periods where there is no overlap.

Here is an illustration of what I am after. The hours in the coloured cells is what I am expecting back and includes example for employee Adam Smith only.

[url]http://imgur.com/cU4ivW5[/url]

SQL Fiddle example

[url]http://sqlfiddle.com/#!3/e973c/1[/url]

This is the results I would expect back.

EMPLOYEENAME    GROUPNAME   STARTDATE           ENDDATE             WORKHOURS
Adam Smith      Primary     January, 01 2014    January, 31 2014    50
Adam Smith      Tertiary    January, 01 2014    January, 31 2014    50
Adam Smith      Primary     February, 01 2014   February, 28 2014   50
Adam Smith      Secondary   February, 01 2014   February, 28 2014   50
Adam Smith      Primary     March, 01 2014      March, 31 2014      100
Adam Smith      Primary     May, 01 2014        May, 31 2014        100
Adam Smith      Primary     June, 01 2014       June, 30 2014       50
Adam Smith      Tertiary    June, 01 2014       June, 30 2014       50
Adam Smith      Primary     July, 01 2014       July, 31 2014       100
Adam Smith      Primary     August, 01 2014     August, 31 2014     50
Adam Smith      Secondary   August, 01 2014     August, 31 2014     50
Adam Smith      Secondary   September, 01 2014  September, 30 2014  50
Adam Smith      Tertiary    September, 01 2014  September, 30 2014  50
Adam Smith      Primary     November, 01 2014   November, 30 2014   100
Adam Smith      Primary     January, 01 2015    February, 28 2015   100
Adam Smith      Secondary   January, 01 2015    February, 28 2015   100
Julie Tyler     Primary     January, 01 2014    January, 31 2014    100
Julie Tyler     Primary     February, 01 2014   March, 31 2014      50
Julie Tyler     Secondary   February, 01 2014   March, 31 2014      50
Julie Tyler     Secondary   April, 01 2014      July, 31 2014       100
Julie Tyler     Secondary   November, 01 2014   November, 14 2014   100
Julie Tyler     Secondary   November, 15 2014   November, 30 2014   50
Julie Tyler     Tertiary    November, 15 2014   November, 30 2014   50
Julie Tyler     Tertiary    December, 01 2014   January, 31 2015    100

Can the above be achieved using a SQL view or I would need to use stored procedures/functions?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-08 : 13:01:36

CAn you please post your table definition and some sample data? (as insert into)

hanifbarik
Starting Member

9 Posts

Posted - 2014-09-08 : 13:37:38

Here is the table definition and sample data.


create table EMPLOYEE (
  EMPLOYEEID int, 
  EMPLOYEENAME varchar(200), 
  WORKHOURS int
);

insert into EMPLOYEE (EMPLOYEEID, EMPLOYEENAME, WORKHOURS)
values (1, 'Adam Smith', 100);

insert into EMPLOYEE (EMPLOYEEID, EMPLOYEENAME, WORKHOURS)
values (2, 'Julie Tyler', 100);

create table GROUPS (
  GROUPID int, 
  GROUPNAME varchar(200)
);

insert into GROUPS (GROUPID, GROUPNAME)
values (1, 'Primary')

insert into GROUPS (GROUPID, GROUPNAME)
values (2, 'Secondary')

insert into GROUPS (GROUPID, GROUPNAME)
values (3, 'Tertiary')

create table EMPLOYEEGROUPS (
  EMPLOYEEID int, 
  GROUPID int, 
  STARTDATE datetime, 
  ENDDATE datetime
);

insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (1, 1, '2014-01-01', '2014-03-31');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (1, 1, '2014-05-01', '2014-08-31');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (1, 1, '2014-11-01', '2014-11-30');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (1, 1, '2015-01-01', '2015-02-28');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (1, 2, '2014-02-01', '2014-02-28');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (1, 2, '2014-08-01', '2014-09-30');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (1, 2, '2015-01-01', '2015-02-28');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (1, 3, '2014-01-01', '2014-02-28');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (1, 3, '2014-06-01', '2014-06-30');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (1, 3, '2014-09-01', '2014-09-30');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (2, 1, '2014-01-01', '2014-03-31');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (2, 2, '2014-02-01', '2014-07-31');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (2, 2, '2014-11-01', '2014-11-30');
insert into EMPLOYEEGROUPS (EMPLOYEEID, GROUPID, STARTDATE, ENDDATE)
values (2, 3, '2014-11-15', '2015-01-31');

hanifbarik
Starting Member

9 Posts

Posted - 2014-09-08 : 14:26:57

I have also added a column to EMPLOYEEGROUPS table to mimic a primary key.


-- Add an integer column
alter table EMPLOYEEGROUPS
add EMPLOYEEGROUPSID int

-- Generate a sequential list of numbers
;with CTE_PKGENERATOR as (
select *,
ROW_NUMBER() OVER(ORDER BY EMPLOYEEID) AS rn
from EMPLOYEEGROUPS
)

-- Update new column with number sequence
update e
set e.EMPLOYEEGROUPSID = c.rn
from EMPLOYEEGROUPS e
inner join CTE_PKGENERATOR c on c.EMPLOYEEID = e.EMPLOYEEID and c.GROUPID = e.GROUPID and c.STARTDATE = e.STARTDATE and c.ENDDATE = e.ENDDATE

hanifbarik
Starting Member

9 Posts

Posted - 2014-09-08 : 14:39:07

I've had an attempt at trying to find the answer but I suspect I've taken a sledge hammer to crack a nut.

Essentially, I am taking the date range for each group (primary, secondary and tertiary) and splitting to days. This as you can image creates a massive result set - a row (for each day) between each date range for each group for each employee.

I then flag a 2 to indicate any days for primaries which overlap with secondary or tertiary. I do the same for secondary, flag a 2 if days overlap with primary and tertiary and again if tertiary overlaps with the other 2 groups.

I suspect this approach may be completely over-the-top for a large amount of data.

The result set I get back isn't quite correct as when I group the data, it's grouping non-sequential days, for example, May 2014 with July 2015 even though these should be separate as there is an overlap in June. See image above.

Anyway, for what it's worth, here is the query.

Thanks.


;with CTE_PRIMARY
as (
select eg.EMPLOYEEGROUPSID, e.EMPLOYEEID, e.EMPLOYEENAME, g.GROUPID, g.GROUPNAME, eg.STARTDATE, eg.ENDDATE, e.WORKHOURS  
from EMPLOYEEGROUPS eg
inner join EMPLOYEE e on e.EMPLOYEEID = eg.EMPLOYEEID
inner join GROUPS g on g.GROUPID = eg.GROUPID
where g.GROUPNAME = 'Primary'
	UNION all
select EMPLOYEEGROUPSID, EMPLOYEEID, EMPLOYEENAME, GROUPID, GROUPNAME, DATEADD(d,1,STARTDATE), ENDDATE, WORKHOURS
from CTE_PRIMARY
where STARTDATE < ENDDATE
),
CTE_SECONDARY
as (
select eg.EMPLOYEEGROUPSID, e.EMPLOYEEID, e.EMPLOYEENAME, g.GROUPID, g.GROUPNAME, eg.STARTDATE, eg.ENDDATE, e.WORKHOURS  
from EMPLOYEEGROUPS eg
inner join EMPLOYEE e on e.EMPLOYEEID = eg.EMPLOYEEID
inner join GROUPS g on g.GROUPID = eg.GROUPID
where g.GROUPNAME = 'Secondary'
	UNION all
select EMPLOYEEGROUPSID, EMPLOYEEID, EMPLOYEENAME, GROUPID, GROUPNAME, DATEADD(d,1,STARTDATE), ENDDATE, WORKHOURS
from CTE_SECONDARY
where STARTDATE < ENDDATE
),
CTE_TERTIARY
as (
select eg.EMPLOYEEGROUPSID, e.EMPLOYEEID, e.EMPLOYEENAME, g.GROUPID, g.GROUPNAME, eg.STARTDATE, eg.ENDDATE, e.WORKHOURS  
from EMPLOYEEGROUPS eg
inner join EMPLOYEE e on e.EMPLOYEEID = eg.EMPLOYEEID
inner join GROUPS g on g.GROUPID = eg.GROUPID
where g.GROUPNAME = 'Tertiary'
	UNION all
select EMPLOYEEGROUPSID, EMPLOYEEID, EMPLOYEENAME, GROUPID, GROUPNAME, DATEADD(d,1,STARTDATE), ENDDATE, WORKHOURS
from CTE_TERTIARY
where STARTDATE < ENDDATE
),
-- select * from CTE_PRIMARY option (maxrecursion 0) -- 392
-- select * from CTE_SECONDARY option (maxrecursion 0) -- 359
-- select * from CTE_TERTIARY option (maxrecursion 0) -- 197
CTE_OVERLAP as (
select *, 
case
	when exists (select 1 from CTE_SECONDARY s where s.EMPLOYEEID = p.EMPLOYEEID and s.STARTDATE = p.STARTDATE) 
	or exists (select 1 from CTE_TERTIARY t where t.EMPLOYEEID = p.EMPLOYEEID and t.STARTDATE = p.STARTDATE)
	then 2
	else 1
	end
 as OverlapDate
from CTE_PRIMARY p
union all
select *, 
case
	when exists (select 1 from CTE_PRIMARY p where p.EMPLOYEEID = s.EMPLOYEEID and p.STARTDATE = s.STARTDATE)
	or exists (select 1 from CTE_TERTIARY t where t.EMPLOYEEID = s.EMPLOYEEID and t.STARTDATE = s.STARTDATE)
	then 2
	else 1
	end
 as OverlapDate
from CTE_SECONDARY s
union all
select *, 
case
	when exists (select 1 from CTE_PRIMARY p where p.EMPLOYEEID = t.EMPLOYEEID and p.STARTDATE = t.STARTDATE) 
	or exists (select 1 from CTE_TERTIARY t where t.EMPLOYEEID = t.EMPLOYEEID and t.STARTDATE = t.STARTDATE)
	then 2
	else 1
	end
 as OverlapDate
from CTE_TERTIARY t
)
--select *,
--STARTDATE - ROW_NUMBER() OVER(PARTITION BY EMPLOYEEGROUPSID, GROUPNAME ORDER BY STARTDATE) AS rn
-- from CTE_OVERLAP
--order by EMPLOYEENAME, GROUPNAME, STARTDATE
--option (maxrecursion 0)
 -- Total 948

select EMPLOYEEGROUPSID, EMPLOYEENAME, GROUPNAME, OverlapDate, min(STARTDATE) as STARTDT, max(STARTDATE) as ENDDT, min(WORKHOURS) as WRKHRS
from CTE_OVERLAP
group by EMPLOYEEGROUPSID, EMPLOYEENAME, GROUPNAME, OverlapDate
order by EMPLOYEENAME, STARTDT
option (maxrecursion 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources