How to compare Field in ROW1 to field in ROW2

• edward.adu

  SSC Enthusiast

  Points: 119

  More actions

  May 17, 2009 at 2:19 pm

  #132283

  I need to compare END_DT field from ROW1 to BGN_DT date field in ROW2 to make sure the dates are continuos.

  Example

  ID BGN_DT END_DT

  1 7/7/2005 7/28/2005

  1 7/29/2005 8/23/2005

  1 8/29/2005 9/6/2005

  1 9/7/2005 10/7/2005

  2 7/7/2006 7/28/2006

  2 8/6/2006 8/23/2006

  2 8/24/2006 9/6/2006

  2 9/7/2006 10/7/2006

  I am not sure whether to use Transpose or Cursor to accomplish this.

  Can you provide a sample code..

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 17, 2009 at 2:27 pm

  #996065

  If you're using SQL 2005 or 2008, you can use RowNumber.

  Roughly, something like this

  ;With ContigDates (ID, BeginDt, EndDt, RowNo) AS (

  SELECT ID, BeginDate, EndDate, ROW_NUMBER() OVER (Partition by ID Order by BeginDt) AS RowNo

  FROM SomeTable

  )

  SELECT *

  FROM SomeTable T1 LEFT OUTER JOIN SomeTable T2 ON T1.ID = T2.ID AND T1.RowNo = T2.RowNo-1

  WHERE T1.EndDate = DATEADD(dd,-1,T2.StartDate)

  May not be exact and that where clause is possibly the wrong way around, but it should give you an idea.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• joseph s

  SSC Journeyman

  Points: 88

  More actions

  May 19, 2009 at 3:00 am

  #996785

  Heres the exact code

  declare @sometable table(id int, beg_date datetime,end_date datetime,rank int)

  insert into @sometable values(1,'7/7/2005' ,'7/28/2005',null)

  insert into @sometable values(1,'7/29/2005' ,'8/23/2005',null)

  insert into @sometable values(1,'8/29/2005' ,'9/6/2005',null)

  insert into @sometable values(1,'9/7/2005' ,'10/7/2005',null)

  insert into @sometable values(1,'7/7/2006' ,'7/28/2006',null)

  insert into @sometable values(2,'8/6/2006' ,'8/23/2006',null)

  insert into @sometable values(2,'8/24/2006' ,'9/6/2006',null)

  insert into @sometable values(2,'7/7/2006' ,'7/28/2006',null)

  insert into @sometable values(2,'9/7/2006' ,'10/7/2006',null)

  ;WITH contig_dates(id,beg_date,end_date,rank) AS

  (SELECT id,beg_date,end_date,row_number() over (order by id) as rank

  from @sometable)

  select * from contig_dates A inner join contig_dates B on a.id=b.id and A.rank = b.rank-1

  where (b.beg_date) (a.end_date + 1)