help with query to compress the adjacent records into one

Question

Post reply

help with query to compress the adjacent records into one

Sridhar-137443

Hall of Fame

Points: 3762
More actions
February 18, 2010 at 9:25 am

#386337

Hi,
I have the data in the following format.
Company Empno Id CourseId Startdate Stopdate
TTT 1 01 11 9/5/2006 8/27/2007
TTT 1 01 11 8/28/2007 3/4/2008
TTT 1 01 11 3/5/2008 4/20/2008
TTT 1 01 11 4/21/2008 4/23/2008
TTT 1 02 11 4/24/2008 9/20/2009
TTT 1 02 11 1/1/2010 12/31/9999
I want to collapse all the adjacent records into one so that it looks like this
Company Empno Id CourseId Startdate Stopdate
TTT 1 01 11 9/5/2006 4/23/2008
TTT 1 02 11 4/24/2008 9/20/2009
TTT 1 02 11 1/1/2010 12/31/9999
Thanks,
sridhar.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Can you expand on this?

It appears that you are removing the first group of records...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 2

Hi Sri;

From the EmpID = 1 it tells that the startdate is the min of all and the enddate us the max of all, is that it?

just inner join that table twice, and take min of startdate from one instance and take the max of enddate from the another.

-r ww; -n rmudugal;

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 3

No problem... if you post the data in a readily consumable format like what is done in the first link in my signature below, I'm sure that some of the heavy hitters will jump in to help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

surya-434952 SSC Eights! Points: 952 More actions · Answer 4

Hi,

Hopefully i understood ur problem well

Here is the solution

create table #Example

(

Company varchar(10),

Empno int,

Id int,

CourseId int,

Startdate datetime,

Stopdate datetime

)

insert into #Example(Company, Empno, Id, CourseId, Startdate, Stopdate)

select 'TTT', 1, 01, 11, '9/5/2006','8/27/2007'

union all

select 'TTT', 1, 01, 11, '8/28/2007', '3/4/2008'

union all

select 'TTT', 1, 01, 11, '3/5/2008', '4/20/2008'

union all

select 'TTT', 1, 01, 11, '4/21/2008', '4/23/2008'

union all

select 'TTT', 1, 02, 11, '4/24/2008', '9/20/2009'

union all

select 'TTT', 1, 02, 11, '1/1/2010', '12/31/9999'

select t1.Company,t1.Empno,t1.Id,t1.CourseId,min(t1.Startdate)as Startdate,max(t1.Stopdate)as Stopdate

from #Example t1

inner join #Example t2

on t1.Id=t2.Id and t1.Id=1

group by t1.Company,t1.Empno,t1.Id,t1.CourseId

union

select Company,Empno,Id,CourseId,Startdate,Stopdate

from #Example

where Id=2

surya-434952 SSC Eights! Points: 952 More actions · Answer 5

Here is another solution CTE and this is more genric than earlier solution

create table #Example

(

Company varchar(10),

Empno int,

Id int,

CourseId int,

Startdate datetime,

Stopdate datetime

)

insert into #Example(Company, Empno, Id, CourseId, Startdate, Stopdate)

select 'TTT', 1, 01, 11, '9/5/2006','8/27/2007'

union all

select 'TTT', 1, 01, 11, '8/28/2007', '3/4/2008'

union all

select 'TTT', 1, 01, 11, '3/5/2008', '4/20/2008'

union all

select 'TTT', 1, 01, 11, '4/21/2008', '4/23/2008'

union all

select 'TTT', 1, 02, 11, '4/24/2008', '9/20/2009'

union all

select 'TTT', 1, 02, 11, '1/1/2010', '12/31/9999'

;WITH Example_CTE (Company, Empno, Id,CourseId,StartDate,StopDate)

AS

(

select t1.Company,t1.Empno,t1.Id,t1.CourseId, min(t1.Startdate)as StartDate,max(t2.Stopdate)as StopDate from #Example t1

inner join #Example t2

on t1.Id=t2.Id

where month(t2.Startdate)-month(t1.Stopdate)=1

group by t1.Company,t1.Empno,t1.Id,t1.CourseId

)

select Company, Empno, Id,CourseId,StartDate,StopDate from Example_CTE

union

select Company,Empno,Id,CourseId,StartDate,StopDate

from #Example

where ID not in(select Id from Example_CTE)

handkot SSCertifiable Points: 5246 More actions · Answer 6

create table #Example(

Company varchar(10),

Empno int,

Id int,

CourseId int,

Startdate datetime,

Stopdate datetime

)

insert into #Example(Company, Empno, Id, CourseId, Startdate, Stopdate)

select 'TTT', 1, 01, 11, '20060905','20070827'

union all

select 'TTT', 1, 01, 11, '20070828', '20080304'

union all

select 'TTT', 1, 01, 11, '20080305', '20080420'

union all

select 'TTT', 1, 01, 11, '20080421', '20080423'

union all

select 'TTT', 1, 01, 11, '20080425', '20080429'

union all

select 'TTT', 1, 01, 11, '20080430', '20080430'

union all

select 'TTT', 1, 02, 11, '20080424', '20090920'

union all

select 'TTT', 1, 02, 11, '20100101', '20991231'

;with CTE([Key], Company, Empno, Id, CourseId, StartDate, StopDate)

as (

select

t1.StartDate [Key]

, t1.Company

, t1.Empno

, t1.Id

, t1.CourseId

, t1.StartDate

, t1.StopDate

from

#Example t1

where

--if not previous period

not exists (select 1 from #Example t2 where t2.Id = t1.ID and t2.StopDate + 1 = t1.StartDate)

union all

select

CTE.[Key]

, t2.Company

, t2.Empno

, t2.Id

, t2.CourseId

, t2.StartDate

, t2.StopDate

from

#Example t2

inner join CTE on t2.Id = CTE.Id and t2.StartDate = CTE.StopDate + 1

)

select

Company

, Empno

, Id

, CourseId

, Min(StartDate) StartDate

, Max(StopDate) StopDate

from

CTE

group by

[Key]

, Company

, Empno

, Id

, CourseId

I Have Nine Lives You Have One Only
THINK!

surya-434952 SSC Eights! Points: 952 More actions · Answer 7

Hi,

Here adjacent records means consecutive date/days or consecutive months??

Surya

handkot SSCertifiable Points: 5246 More actions · Answer 8

The continuous period when the end of one coincides with the beginning of another

first period

'20060905','20070827'

'20070828', '20080304'

'20080305', '20080420'

'20080421', '20080423'

second

'20080425', '20080429'

'20080430', '20080430'

third

'20080424', '20090920'

fourth

'20100101', '20991231'

I Have Nine Lives You Have One Only
THINK!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Ah, I missed all the action. It is 3:48 AM here. While I was sleeping you guys were hard at work:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 10

try this article on my blog

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/08/27/sql-and-contiguous-data-ranges.aspx

Clear Sky SQL
My Blog[/url]

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 11

Hi,

sorry for the late reply. I will look at your solutions. When I said adjacent I mean continuous. so for example if I have dates like this for same employee

1/1/2010 - 1/5/2010

1/6/2010 - 1/18/2010

1/20/2010 - 1/25/2010

1/30/2010 - 2/8/2010.

The result should be

1/1/2010 - 1/18/2010

1/20/2010 - 1/25/2010

1/30/2010 - 2/8/2010.

Thanks,

sridhar.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 12

My article will help you with that

Clear Sky SQL
My Blog[/url]

handkot SSCertifiable Points: 5246 More actions · Answer 13

My reply will help you too

Dave Ballantyne

It's a good article

but we need one more table "Calendar"

and i don't know if it is possible for Sridhar-137443

I Have Nine Lives You Have One Only
THINK!

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 14

handkot (2/19/2010)
My reply will help you too

Compare the two methods over a large dataset (1 mill rows at least) . The method i documented will on require a single scan of the data your recursive cte will require that plus many lookups.

It's a good article
but we need one more table "Calendar"

Thats just a simple calendar table ,i did give the link , IMO an essential tool. http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

and i don't know if it is possible for Sridhar-137443

It will be , it may take some understanding of the method but it will work.

Clear Sky SQL
My Blog[/url]