Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Aggregating Intervals

By Michael LeClair,

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

Total article views: 858 | Views in the last 30 days: 10
 
Related Articles
FORUM

Are sleeping transactions are really sleeping

Are sleeping transactions are really sleeping

FORUM

users in sleeping mode

users in sleeping mode

FORUM

Problem whit insert into table.

I have problem whit inserting rows into a table (Personal).

FORUM

Sleeping process

Activity monitor says a job is sleeping but Physical IO changes

FORUM

Insert rows

insert

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones