help with query to compress the adjacent records into one

• Sridhar-137443

  Hall of Fame

  Points: 3698

  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.

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  February 18, 2010 at 1:23 pm

  #1121301

  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

  February 18, 2010 at 9:30 pm

  #1121554

  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: 1000539

  More actions

  February 18, 2010 at 10:08 pm

  #1121561

  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)
  Intro to Tally Tables and Functions

• surya-434952

  SSC Eights!

  Points: 952

  More actions

  February 18, 2010 at 10:18 pm

  #1121564

  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

  February 18, 2010 at 11:19 pm

  #1121569

  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

  SSCarpal Tunnel

  Points: 4956

  More actions

  February 19, 2010 at 12:34 am

  #1121589

  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

  February 19, 2010 at 1:36 am

  #1121613

  Hi,

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

  Surya

• handkot

  SSCarpal Tunnel

  Points: 4956

  More actions

  February 19, 2010 at 1:39 am

  #1121615

  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

  February 19, 2010 at 1:50 am

  #1121618

  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: 33556

  More actions

  February 19, 2010 at 2:09 am

  #1121626

  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: 3698

  More actions

  February 19, 2010 at 7:56 am

  #1121856

  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: 33556

  More actions

  February 19, 2010 at 8:18 am

  #1121869

  My article will help you with that

  ---

  
  Clear Sky SQL
  My Blog[/url]

• handkot

  SSCarpal Tunnel

  Points: 4956

  More actions

  February 19, 2010 at 8:56 am

  #1121909

  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: 33556

  More actions

  February 19, 2010 at 9:18 am

  #1121928

  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]