Collaspe Logic for Dates

• vp7986

  SSC Enthusiast

  Points: 167

  More actions

  August 22, 2012 at 9:56 am

  #393003

  First I want to Thanks for Reading this.

  SOURCE:-

  NM_ID STRT_DT END_DT

  ABD 01/10/2010 01/11/2010

  ABD 01/11/2010 01/12/2011

  ABD 01/12/2011 01/01/9999

  BBD 01/20/2010 01/20/2011

  BBD 01/20/2012 01/01/9999

  TARGET:-

  NM_ID STRT_DT END _DT

  ABD 01/10/2010 01/01/9999

  BBD 01/20/2010 01/20/2011

  BBD 01/20/2012 01/01/9999

  By seeing the above example while extracting the data from source table to target i want to use if Particular Id END_DT=STRT_DT then collaspe it to One Line instead of 2 lines.

  Please provide me the logic in SQL

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  August 22, 2012 at 11:56 am

  #1528822

  --edit. Removed post.

  ---

• vp7986

  SSC Enthusiast

  Points: 167

  More actions

  August 22, 2012 at 12:10 pm

  #1528833

  this logic will only apply if there are only 2 matching rows.

  If there are multiple i cannot get the match.

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  August 22, 2012 at 12:21 pm

  #1528839

  vp7986 (8/22/2012)

  ---

  this logic will only apply if there are only 2 matching rows.

  If there are multiple i cannot get the match.

  Sorry, my first post was rubbish. Try this:

  create table #temp (

  NM_ID char(3)

  ,STRT_DT date

  ,END_DT date

  )

  insert #temp

  select 'ABD', '01/10/2010', '01/11/2010' union all

  select 'ABD', '01/11/2010', '01/12/2011' union all

  select 'ABD', '01/12/2011', '01/01/9999' union all

  select 'BBD', '01/20/2010', '01/20/2011' union all

  select 'BBD', '01/20/2012', '01/01/9999'

  select * from #temp

  select t1.nm_id, min(t1.strt_dt) strt_dt, max(t2.end_dt) End_dt

  from #temp t1

  join #temp t2 on t1.End_Dt = t2.Strt_dt

  group by t1.nm_id

  union select t1.nm_id, t1.strt_dt, t1.end_dt

  from #temp t1 where not exists (select 1 from #temp t2 where t2.nm_id = t1.nm_id and (t2.strt_dt = t1.end_dt or t1.strt_dt = t2.end_dt ))

  ---

• vp7986

  SSC Enthusiast

  Points: 167

  More actions

  August 22, 2012 at 1:11 pm

  #1528877

  Thanks Pro this Is understandable.

  I hope this will work in my Actual Example.

  Thanks For you Time.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 22, 2012 at 2:29 pm

  #1528920

  Here is a possible alternative. I haven't compared it to Phil's solution.

  create table #temp (

  NM_ID char(3)

  ,STRT_DT date

  ,END_DT date

  );

  insert #temp

  select 'ABD', '01/10/2010', '01/11/2010' union all

  select 'ABD', '01/11/2010', '01/12/2011' union all

  select 'ABD', '01/12/2011', '01/01/9999' union all

  select 'BBD', '01/20/2010', '01/20/2011' union all

  select 'BBD', '01/20/2012', '01/01/9999' union all

  select 'CBD', '01/10/2010', '01/11/2010' union all

  select 'CBD', '01/11/2010', '01/12/2011' union all

  select 'CBD', '01/12/2011', '01/01/9999'

  go

  with BaseData as (

  select

  t1.NM_ID,

  t1.STRT_DT,

  t1.END_DT,

  row_number() over (order by t1.NM_ID, t1.STRT_DT) rn,

  t2.NM_ID as NM_ID2

  from

  #temp t1

  left outer join #temp t2

  on (t1.NM_ID = t2.NM_ID and

  t1.STRT_DT = t2.END_DT)

  ), rCTE as (

  select

  bd1.NM_ID,

  bd1.STRT_DT,

  bd1.END_DT,

  bd1.rn

  from

  BaseData bd1

  where

  bd1.NM_ID2 is null

  union all

  select

  bd1.NM_ID,

  bd2.STRT_DT,

  bd2.END_DT,

  bd1.rn

  from

  rCTE bd1

  inner join #temp bd2

  on (bd1.NM_ID = bd2.NM_ID and

  bd2.STRT_DT = bd1.END_DT)

  )

  select

  NM_ID,

  min(STRT_DT) STRT_DT,

  max(END_DT) END_DT

  from

  rCTE

  group by

  NM_ID,

  rn

  order by

  NM_ID,

  STRT_DT;

  go

  drop table #temp;

  go

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  August 22, 2012 at 2:31 pm

  #1528922

  Here is a possible alternative. I haven't compared it to Phil's solution.

  Actually, my name is now 'Pro'

  ---

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 22, 2012 at 2:38 pm

  #1528928

  Phil Parkin (8/22/2012)

  ---

  Here is a possible alternative. I haven't compared it to Phil's solution.

  Actually, my name is now 'Pro'

  oops :blush:

  I'll try to remember that in the future.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 22, 2012 at 3:26 pm

  #1528962

  Phil Parkin (8/22/2012)

  ---

  vp7986 (8/22/2012)

  ---

  this logic will only apply if there are only 2 matching rows.

  If there are multiple i cannot get the match.

  Sorry, my first post was rubbish. Try this:

  create table #temp (

  NM_ID char(3)

  ,STRT_DT date

  ,END_DT date

  )

  insert #temp

  select 'ABD', '01/10/2010', '01/11/2010' union all

  select 'ABD', '01/11/2010', '01/12/2011' union all

  select 'ABD', '01/12/2011', '01/01/9999' union all

  select 'BBD', '01/20/2010', '01/20/2011' union all

  select 'BBD', '01/20/2012', '01/01/9999'

  select * from #temp

  select t1.nm_id, min(t1.strt_dt) strt_dt, max(t2.end_dt) End_dt

  from #temp t1

  join #temp t2 on t1.End_Dt = t2.Strt_dt

  group by t1.nm_id

  union select t1.nm_id, t1.strt_dt, t1.end_dt

  from #temp t1 where not exists (select 1 from #temp t2 where t2.nm_id = t1.nm_id and (t2.strt_dt = t1.end_dt or t1.strt_dt = t2.end_dt ))

  Had a chance to test this a bit. Mine is slower against the same data, has more reads and sorts, but 1 fewer table scan with 3+ a little more times the reads.

  Hey, it was interesting to work and an opportunity to try a recursive cte.