I'm not sure if this is article worthy or not. I had a requirement for which I couldn't find a solution, so I developed my own.
A Simple business case would be where a case worker is assigned a case number or an Employee is responsible for a Contract.
Data is stored in a 3 column table, such as:
EmplID, CaseNum, EffDate
or
EmplID, Contract, EffDate
begin try drop table #tmpTester end try begin catch end catch
create table #tmpTester (
[Person] varchar(128),
Task varchar(128),
EffDate varchar(10)
)
insert #tmpTester values ('Bob', 'eat', '2001-10-01')
insert #tmpTester values ('Bob', 'eat', '2003-01-01')
insert #tmpTester values ('Sue', 'eat', '2004-07-01')
insert #tmpTester values ('Bob', 'eat', '2004-08-01')
insert #tmpTester values ('Bob', 'eat', '2005-01-01')
insert #tmpTester values ('Bob', 'eat', '2005-06-01')
insert #tmpTester values ('Fred', 'eat', '2005-07-01')
insert #tmpTester values ('Fred', 'eat', '2006-01-01')
insert #tmpTester values ('Fred', 'eat', '2007-01-01')
insert #tmpTester values ('Fred', 'eat', '2008-01-01')
insert #tmpTester values ('Fred', 'eat', '2008-04-01')
insert #tmpTester values ('Sue', 'eat', '2008-06-01')
insert #tmpTester values ('Sue', 'eat', '2009-02-01')
insert #tmpTester values ('Fred', 'sleep', '2001-04-01')
insert #tmpTester values ('Fred', 'sleep', '2003-01-01')
insert #tmpTester values ('Fred', 'sleep', '2004-03-01')
insert #tmpTester values ('Bob', 'sleep', '2004-05-01')
insert #tmpTester values ('Sue', 'sleep', '2005-01-01')
insert #tmpTester values ('Bob', 'sleep', '2005-04-01')
insert #tmpTester values ('Sue', 'sleep', '2005-09-01')
insert #tmpTester values ('Fred', 'sleep', '2006-02-01')
insert #tmpTester values ('Fred', 'sleep', '2008-01-01')
insert #tmpTester values ('Sue', 'sleep', '2008-03-01')
insert #tmpTester values ('Sue', 'sleep', '2008-10-01')
insert #tmpTester values ('Sue', 'sleep', '2009-01-01')
insert #tmpTester values ('Sue', 'sleep', '2009-11-01')
select * from #tmpTester
begin try drop table #tmpRanges end try begin catch end catch
;with tmpTT(rowid, Person, Task, EffDate) as (select row_number() over(order by Task, EffDate, Person), * from #tmpTester)
select
a.*,
case when b.EffDate is null then convert(varchar(10), getdate(), 120) else b.EffDate end as Until into #tmpRanges
from
tmpTT a
left join tmpTT b
on a.Task = b.Task
and a.rowid + 1 = b.rowid
Using a self join on row_numbers gives you date ranges and covers your timeline. The ultimate goal is to aggregate like assignments that are also adjacent. For example, Bob could be assigned to case# 3 multiple times consecutively, then Alice, then back to Bob.
Using row_number and partitioning over your 2 other columns, together with the original rowids can collectively give you something to group by to accomplish the desired outcome.
begin try drop table #tmpGroupedRanges end try begin catch end catch
select
Task,
EffDate,
Until,
Person,
(2*rowid) - row_number() over(partition by Task order by Task, EffDate) - row_number() over(partition by Task, Person order by Task, EffDate) as TwoxRowidLessTaskLessTaskPerson into #tmpGroupedRanges
from
#tmpRanges
order by
rowid
;with tmpBOOYAH (Task, FromMonth, Until, Person) as (
select
Task,
min(EffDate) as FromMonth,
max(Until) as Until,
Person
from
#tmpGroupedRanges
group by
Task,
Person,
TwoxRowidLessTaskLessTaskPerson
)
select *, datediff(month, cast(FromMonth as smalldatetime), cast(Until as smalldatetime)) as MonthCount
from tmpBOOYAH
order by Task,FromMonth
